PGRST116 Means Zero or Many Rows: How One Double-Tap Grew 2,008 Duplicate Bookings

TL;DR — Students book tomorrow’s meals in an app. The booking endpoint checked for an existing booking with supabase-js .single(), and if the error code was PGRST116, it treated that as “no booking yet” and inserted one. But PostgREST returns PGRST116 for “more than 1 or no items”. So once a double-tap race created two rows for one person and one meal, every later tap read those two rows as zero, inserted a third, and the pile grew. The table had no unique constraint to stop it. Over 26 months that produced 2,008 extra rows in 1,013 groups, with one person collecting 25 extra rows in a single day. The fix is a unique constraint on the business fact, in a specific order: make the code tolerate a unique violation first, delete the duplicates second, add the constraint third.

Key takeaways

• An error code that means two things will be read as one of them. PGRST116 means “not exactly one row”, and code that reads it as “no row” turns a duplicate into more duplicates.
• Check-then-insert without a unique constraint does not leak an occasional duplicate. With an ambiguous check, it compounds.
• The database is the only place that can make “one per person, per meal, per day” true. Application checks decide the response; the constraint decides what is possible.
• Fix in order: code that handles 23505 → dedupe → constraint. Any other order either fails to apply or breaks writes in between.
• A retried request needs an idempotency key, and a duplicate the database refused is a normal answer, not a 500.

The code that looked careful

The booking handler did what most handlers do: look first, then write.

// Illustrative. BROKEN.
const { data: existing, error } = await db
  .from('bookings')
  .select('id, response')
  .eq('customer_id', customerId)
  .eq('menu_id', menuId)
  .eq('booking_date', date)
  .single();

if (error && error.code !== 'PGRST116') throw error;   // PGRST116 = "no row", surely

if (existing) {
  await db.from('bookings').update({ response }).eq('id', existing.id);
} else {
  await db.from('bookings').insert({ customer_id: customerId, menu_id: menuId, booking_date: date, response });
}

It even reads as defensive: the unexpected errors are rethrown, and the one expected error is handled. Elsewhere in the same service, an atomic upsert(…, { onConflict }) had been written and then commented out, because onConflict needs a unique index to name, and the table did not have one. Somebody had reached for the right tool and found it missing.

What PGRST116 actually means

.single() asks PostgREST for a singular response: one JSON object instead of an array. When the query does not return exactly one row, PostgREST refuses with HTTP 406 and this entry from its error reference:

PGRST116 — “More than 1 or no items where returned when requesting a singular response.”

Zero rows and two rows produce the same code. The client library surfaces it as the familiar “JSON object requested, multiple (or no) rows returned”. Nothing in the error object tells the caller which case it is in, so any code that branches on PGRST116 alone is guessing, and the natural guess — “no row” — is the dangerous one.

.maybeSingle() returns null instead of an error for zero rows, but it still errors on more than one. On its own it would have turned the silent pile-up into a loud failure, which is better, but not a fix.

How one duplicate becomes thirty

The first duplicate needs a race. Two taps on “I’ll eat” a few hundred milliseconds apart, on a slow network, are two requests that both run the check before either inserts:

  tap 1 ──check──▶ 0 rows ─────────────insert──▶ row A
  tap 2 ────check──▶ 0 rows ─────────────insert──▶ row B        ← race: 2 rows

  tap 3 ──check──▶ 2 rows ──▶ PGRST116 ──▶ "no row" ──insert──▶ row C
  tap 4 ──check──▶ 3 rows ──▶ PGRST116 ──▶ "no row" ──insert──▶ row D
  …

  ▼  after the first race, every tap is an insert — no race required

That is the part worth being precise about. The race is rare and it only has to happen once. After that, the check is deterministic: every future tap by that person, for that meal, on that day, sees “not exactly one”, reads “none”, and adds a row. Each tap also carried the person’s current answer, so a student who changed their mind three times left three rows with alternating answers.

The numbers

A read-only query grouped the table by the business key — customer, menu, date — and counted everything past the first row:

It was not an incident. It was a standing bug with a steady rate for 26 months, which is why nobody had reported it. The concentration at two canteens fits the mechanism: those are the sites where students book the next day’s meals themselves, from a phone, which is the path where a double-tap is easiest. None of the duplicates came from the staff path.

The damage was quieter than the numbers suggest, and it was two things. Booked counts in the kitchen’s reports were inflated, usually by one or two a day, occasionally by twenty or thirty. And people with a duplicate could not change their booking from the app at all: every other write path also looked the row up with .single(), and those paths rethrew PGRST116 as an error. The duplicate made the booking read-only for its owner.

Which duplicate to keep

Deleting duplicates means choosing a survivor, and the choice has to be defensible for every one of the 1,013 groups, not just the typical one.

Keeping the newest row was the right rule here, for a reason specific to this bug: the duplicates were not copies, they were a history. Every change of mind had become a new row instead of an update, so the row with the highest id was literally the last thing each person tapped. Keeping the oldest would have restored their first answer for the 348 people who changed it. The meal options never conflicted, so no group needed a merge.

-- Illustrative. Keep the newest row per business key; delete the rest.
DELETE FROM bookings b
USING (
  SELECT id,
         row_number() OVER (PARTITION BY customer_id, menu_id, booking_date
                            ORDER BY id DESC) AS rn
    FROM bookings
) ranked
WHERE b.id = ranked.id
  AND ranked.rn > 1;

Before running it: the same query as a SELECT count(*), which had to come back as 2,008, and a second query checking whether any group disagreed on anything other than the answer. I would add one step I did not take: a copy of the rows being deleted, into a side table, before the DELETE. The count matching is evidence that the delete is the one you meant. It is not a way back.

Fix it in this order

The constraint is one line. Getting to a state where it can exist is three steps, and the order is the whole trick.

1. Make every writer tolerate a unique violation. Once the constraint exists, a racing insert fails with 23505. The code has to treat that as “someone else just created it” and fall through to an update, or the race that used to create a duplicate now creates a 500.

// Illustrative. FIXED.
const { data: existing, error } = await db
  .from('bookings')
  .select('id')
  .eq('customer_id', customerId).eq('menu_id', menuId).eq('booking_date', date)
  .order('id', { ascending: false })
  .limit(1)
  .maybeSingle();                        // zero rows → null, not an error
if (error) throw error;

if (existing) {
  await db.from('bookings').update({ response }).eq('id', existing.id);
} else {
  const { error: insertError } = await db.from('bookings').insert({ /* … */ });
  if (insertError?.code === '23505') {   // lost the race: the row exists now
    await db.from('bookings').update({ response })
      .eq('customer_id', customerId).eq('menu_id', menuId).eq('booking_date', date);
  } else if (insertError) throw insertError;
}

.order().limit(1) makes the lookup safe even while old duplicates still exist, which matters for the minutes between deploying the code and running the migration.

2. Delete the duplicates. The constraint cannot be created while they exist.

3. Add the constraint, in the same migration as the delete, so no new duplicate can slip in between:

-- Illustrative.
ALTER TABLE bookings
  ADD CONSTRAINT bookings_one_per_meal UNIQUE (customer_id, menu_id, booking_date);

With the constraint in place, the commented-out upsert becomes possible again, and Postgres guarantees an atomic insert-or-update “even under high concurrency”. That is the end state to aim for. The check-then-write with a 23505 fallback is the bridge that lets you get there without a window of broken writes.

Which tool for which job

The rule we chose not to enforce in the database

The same week, the obvious next step was a stricter rule on the serving side: one lunch per person per day across every canteen, as a unique index on meals served. It could not be created. 202 historical groups (205 rows) already violated it, and the decision was to leave that history alone and not add the index.

That rule is enforced in the application instead, and checked: across the 666,109 serves recorded since January 2025 there were 88 violating groups, all but one from January to April 2025. The accepted gap is narrow and specific: two counters at different canteens confirming the same person at the same instant can both succeed. I think that trade is fine for a meal. I would not make it for money, and the per-meal, per-canteen rule that the serving endpoint relies on is still a real unique index.

The other half: a retry is not a new request

The booking bug came from the phone app. The serving counters have the opposite problem: they run offline-first and replay a queue when the network returns, so the same request can arrive several times on purpose. For that, the endpoints now take an Idempotency-Key, built on the claim-first pattern from the serving webhook with three rules that were specific to an offline client:

• Server errors are never stored. A 5xx releases the key instead of recording the response. Replaying a stored 500 would turn a transient failure into a permanent one.
• Same key, different body is a 422. The key is stored with a hash of method, path and body. A reused key with a different payload is a client bug, and retrying it will never help.
• Keys live for seven days. A 24-hour window is not enough for a counter that syncs late after a bad week of connectivity.

And one bug the key exposed: when the database refused a duplicate serve, the endpoint had been answering HTTP 500, “failed to add meal served record”. An offline queue retries a 500 forever. It now answers 200 with already_served, when and where the meal was served — because a duplicate the constraint caught is the system working.

What actually broke in production

All of it, for 26 months, and quietly. That is the uncomfortable part: the bug did not produce an error anyone saw. Duplicates inflated a kitchen count by one or two a day, which reads as noise. The people who could not change their booking got an error from the app, and the error did not say why. Nothing alerted, because nothing was failing in a way the system knew how to call a failure.

The commented-out upsert is the other honest detail. The correct design had been written once, hit the missing constraint, and been set aside for a check-then-insert that worked in testing. I have done the same thing: when the database refuses the right tool, it is tempting to route around the refusal rather than fix the schema. The refusal was the database pointing at the actual bug.

FAQ

What does PGRST116 mean in Supabase? It means a request for a single row — .single() in supabase-js — returned something other than exactly one row. PostgREST uses the same code, with HTTP 406, for zero rows and for more than one row. Your code cannot tell which from the code alone, so never treat PGRST116 as “not found” on a table where duplicates are possible.

What is the difference between single() and maybeSingle()? .single() errors when there are zero rows or more than one. .maybeSingle() returns null for zero rows but still errors for more than one. Use .single() for primary-key lookups, .maybeSingle() for optional rows where a unique constraint guarantees at most one, and add .order().limit(1) when a table may still contain duplicates.

How do I prevent duplicate rows from concurrent requests? Add a unique constraint on the business key — here, customer, menu and date — and handle the unique-violation error (23505) as “already exists”. Checking first in application code cannot prevent a race: two requests can both see no row and both insert. Only the database can make the second insert fail.

How do I add a unique constraint to a table that already has duplicates? Deploy code that tolerates 23505 first. Then, in one migration, delete the duplicates — choosing the survivor by a rule you can defend for every group — and add the constraint. Count the rows to be deleted beforehand and copy them to a side table, so the delete can be undone.

Should I keep the oldest or the newest duplicate row? It depends on what the duplicates represent. If they are accidental copies, either works. If each duplicate recorded a newer user action, as with repeated bookings that flipped an answer, the newest row is the user’s latest intent and the oldest is stale. Check whether groups disagree before deciding.

Should a duplicate request return an error? Not a 5xx. If the database refused a duplicate, the operation already happened; return a success with a status such as already_served. A 500 invites retries, and an offline client will retry it indefinitely. Reserve 409 for a request whose idempotency key is still being processed.

What I’d still improve

• One write path still does the old lookup. It uses .single() for its existence check, with the 23505 fallback on the insert. The constraint makes it safe; it is still the pattern that caused this, and it should go.
• Back up before deleting. Next time, the rows go to a side table before the DELETE, every time.
• Alert on 23505. A unique violation is now the expected response to a race. A sudden rise in them is a client bug in the making, and it is cheap to count.
• Audit every PGRST116 branch in the codebase. Every place that reads it as “not found” is a place that would read a duplicate as “not found”.

The one idea to take away

If a rule must always be true, put it where it cannot be skipped.

The booking code checked for a duplicate on every single request, and the table grew 2,008 of them anyway, because a check is a question and a constraint is a guarantee. The check asked a question whose answer it misread. A unique constraint would have refused the second row in July 2024, and the rest of this post would not exist.

---

I write about backend reliability, webhook and payment correctness, and Postgres data modelling from work on food-tech and healthcare platforms. More on what I build and how I work, the webhook where every business outcome is a 200, and why a dispatcher’s uniqueness lives in the database. If you have a PGRST116 branch that inserts, search for it now.