Supabase PGRST116: Cannot coerce the result to a single JSON object
Zero rows and two rows share one code. The details field says which, and the fix is different: policy, session, or a unique filter.

Introduction & Quick Summary
Symptom. A query that obviously returns one row fails, and the error object is easy to misread:
{
"code": "PGRST116",
"details": "The result contains 0 rows",
"hint": null,
"message": "Cannot coerce the result to a single JSON object"
}Older PostgREST versions phrase the same thing as JSON object requested, multiple (or no) rows returned, which is why searching the second string lands on answers for a different version. Either way the HTTP status is 406 and the client code that expected data gets error.
Cause. .single() asks PostgREST for a singular resource by sending Accept: application/vnd.pgrst.object+json. If the result is not exactly one row, PostgREST answers 406 with PGRST116. Zero rows and two or more rows produce the same code with different details, and the causes are unrelated: a missing row or a row hidden by Row Level Security on one side, a non-unique filter on the other.
Fix. Read details, then choose the right method:
// a missing row is a normal outcome
const { data, error } = await supabase
.from("profiles")
.select("display_name, avatar_url")
.eq("id", userId)
.maybeSingle();
if (error) throw error;
if (!data) return null; // not created yet, or not visible to this user// several rows are legal, take one deterministically
const { data, error } = await supabase
.from("sessions")
.select("id, created_at")
.eq("user_id", userId)
.order("created_at", { ascending: false })
.limit(1)
.maybeSingle();Core Mechanics, Signatures & Reproducible Setup
supabase-js has three shapes for reading rows, and the difference is both the Accept header and the client-side handling:
| Method | Header sent | On 0 rows | On 2+ rows |
|---|---|---|---|
.select() | none | data: [] | data: [...] |
.single() | vnd.pgrst.object+json | error PGRST116 | error PGRST116 |
.maybeSingle() | vnd.pgrst.object+json (or a list) | data: null, no error | error PGRST116 |
.maybeSingle() is implemented in the client: it fetches with a limit and decides locally whether zero, one or many rows came back. That is why it tolerates zero rows without an error and still reports the multiple-row case with the same code.
Reproduce both branches against a local Supabase project with a table that has no policies, so RLS is part of the demonstration:
create table public.profiles (
id uuid primary key references auth.users (id) on delete cascade,
display_name text
);
alter table public.profiles enable row level security;
insert into public.profiles (id, display_name)
values ('00000000-0000-0000-0000-000000000001', 'first'),
('00000000-0000-0000-0000-000000000002', 'second');// 0 rows visible (RLS on, no policy) still returns PGRST116 for .single()
const r1 = await supabase.from("profiles").select("display_name").eq("id", someId).single();
console.log(r1.error?.code, r1.error?.details);
// PGRST116 The result contains 0 rows
// 2 rows for a non-unique filter
const r2 = await supabase.from("profiles").select("display_name").neq("display_name", "").single();
console.log(r2.error?.code, r2.error?.details);
// PGRST116 The result contains 2 rowsThe same request over HTTP shows the header and the status, which is useful when a framework or a proxy hides the client's error object:
curl -s -i "https://PROJECT.supabase.co/rest/v1/profiles?select=display_name&id=eq.$ID" \
-H "apikey: $PUBLISHABLE_KEY" \
-H "Authorization: Bearer $ACCESS_TOKEN" \
-H "Accept: application/vnd.pgrst.object+json" | head -20Key Comparison Table
details value | What happened | Usual cause |
|---|---|---|
The result contains 0 rows | no row matched | row absent, filter wrong, or RLS hid it |
The result contains 2 rows | more than one row matched | filter on a non-unique column |
Results contain 0 rows, application/vnd.pgrst.object+json requires 1 row | older PostgREST wording | same as the first row |
permission denied for table | policy denies the operation | missing grant, not a row count |
| Scenario | Correct method | Why |
|---|---|---|
| profile that may not exist yet | .maybeSingle() | zero rows is a valid state |
| lookup by primary key known to exist | .single() | a missing row is a real error |
| newest row of many | .order().limit(1).maybeSingle() | deterministic pick |
| existence check | select("id", { count: "exact", head: true }) | no row body needed |
| list view | .select() | zero rows is a list, not an error |
Practical Edge Cases, Error Fixes & Deep Dive Code Examples
1. RLS that hides the row you can see in the dashboard
The SQL editor runs as postgres, which has bypassrls, so a row exists there and not through the API. This is the most common "0 rows" surprise:
select policyname, cmd, roles, qual from pg_policies where tablename = 'profiles';create policy "read own profile"
on public.profiles for select
to authenticated
using ((select auth.uid()) = id);A table with RLS enabled and no policy returns zero rows to every anonymous request, which then becomes PGRST116 under .single().
2. Fetching before the session is restored
On page load, a client that reads a profile before Supabase has restored the session from storage sends an anonymous request. The row exists, the policy is correct, and the result is still zero:
const { data: { session } } = await supabase.auth.getSession();
if (!session) return null; // do not query protected rows yet
const { data } = await supabase.from("profiles").select("*")
.eq("id", session.user.id).maybeSingle();Server components have the same trap in a different form: cookies must be attached to the client before the query, or the request is anonymous.
3. Filters that are not unique
An email column without a unique constraint, or a soft-delete flag that leaves two live rows, turns .single() into a 406 the moment the second row appears. Two honest fixes:
// 3a. make the filter unique in the database
// alter table public.profiles add constraint profiles_email_key unique (email);// 3b. or accept the many-row case and pick deterministically
const { data } = await supabase.from("profiles").select("id, updated_at")
.eq("email", email).order("updated_at", { ascending: false }).limit(1).maybeSingle();Adding .limit(1) without order is not a fix: which row you get is up to the planner.
4. Upsert-then-read without a policy for select
An insert policy with select missing allows the write and forbids the read-back. The .single() after the upsert returns PGRST116 with 0 rows even though the insert succeeded, which makes it look like a transaction problem:
create policy "insert own profile"
on public.profiles for insert to authenticated
with check ((select auth.uid()) = id);
create policy "read own profile"
on public.profiles for select to authenticated
using ((select auth.uid()) = id);const { error } = await supabase.from("profiles")
.upsert({ id, display_name }, { onConflict: "id" })
.select("id") // requires a select policy
.single();5. Version differences in the message
PostgREST changed the message text in late 2024 to say what actually happened, and the code stayed PGRST116. Documentation and blog posts still quote JSON object requested, multiple (or no) rows returned, and some of them label it PGRST505. Match on the code, not the string:
if (error?.code === "PGRST116") {
const rows = error.details?.match(/(\d+) row/)?.[1];
// "0" means missing or hidden, "2" means the filter is not unique
}Logging code plus details is what makes this diagnosable in production; a generic "query failed" message throws that away.
6. Server-side equivalents
On the server you often want the same semantics in SQL rather than in the client, and the maybeSingle idea maps to limit 1:
select display_name
from public.profiles
where id = auth.uid()
limit 1;If the caller must distinguish "no row" from "many rows", run a count(*) first or fetch all matching rows and decide in code. Letting PostgREST coerce to an object hides that distinction behind one error code.
Common Compilation/Runtime Mistakes & Version Gotchas
- Using
.single()for a row that may not exist..maybeSingle()exists for exactly this. - Treating PGRST116 as a permissions error. It is a row-count error;
42501is the permission one. - Adding
.limit(1)without anorder. The result is legal but arbitrary. - Reading a protected row before the session is restored. Anonymous requests see nothing under RLS.
- Forgetting a
selectpolicy next to aninsertpolicy. The write works and the read-back fails. - Matching on the message text. The wording changed across PostgREST versions.
- Logging only
error.message.detailsis what tells 0 rows from 2 rows. - Assuming the SQL editor's result matches the API's.
postgresbypasses RLS.
Draft and check the policy conditions with the SQL formatter, and inspect a row payload or an error object with the JSON formatter before deciding which side is wrong.
Reference: singular and plural responses, PostgREST errors, and the supabase-js single reference.
Official references
primary documentationThe SQL documentation that defines the behaviour this guide explains.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQLite: database is locked (SQLITE_BUSY) under concurrent writes
SQLite allows one writer. Set a busy timeout, switch to WAL, start write transactions as IMMEDIATE, and reduce to a single writer connection.
MySQL: ERROR 1040 (HY000) Too many connections
The server is out of slots because pools are too large or leaking. Read the counters, size pools against replicas, then raise the limit.
MySQL 8: Public Key Retrieval is not allowed over JDBC
The driver never sent the password. Use verified TLS, or allow key retrieval only where the network is trusted.
Postgres: invalid input syntax for type uuid
An empty filter or a legacy id reaches a uuid parameter. Treat absent as no filter and validate before the query.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.