You're doing a bulk upsert. You send 100 rows to Postgres using INSERT ... ON CONFLICT DO NOTHING with a RETURNING clause. You expect 100 rows back—the newly inserted ones, plus the rows that already existed. Instead, you get back 94. The other 6 exist in the table; they just don't appear in the result.
This is the most common production bug with ON CONFLICT DO NOTHING. It's not a Postgres bug. It's the documented behavior, and once you understand why it works this way, you'll stop getting surprised by it—and you'll know how to work around it.
What RETURNING Actually Returns
The RETURNING clause in an INSERT statement returns data about rows that were affected by the insert. "Affected" means inserted. Rows that triggered the ON CONFLICT clause were not inserted; they were skipped. So they don't appear in RETURNING.
This is not a gap in the documentation. The Postgres docs say explicitly: "The RETURNING list ... provides the same output format as SELECT, but for rows actually inserted."
With ON CONFLICT DO NOTHING, the conflict rows are silently dropped from the output. Your application receives only the rows that were newly inserted. If you were expecting to use the returned IDs for downstream processing, six rows are now missing from your pipeline, and nothing told you about them.
The DO UPDATE Behavior Is Different, But Has Its Own Traps
With ON CONFLICT DO UPDATE, conflicting rows are updated rather than skipped, and RETURNING does include them. But there's a subtlety: what you get back is the post-update state of the row, not the original row.
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name
RETURNING id, name;If [email protected] already existed with name "Alice B.", the update changes her name to "Alice" and returns the new state. The original name is gone from the result. If your downstream code expected to see both the old and new values, you need to capture them differently.
There's also a concurrency edge case. Under ON CONFLICT DO UPDATE, Postgres acquires a row-level lock on the conflicting row before updating it. If another transaction has that row locked, your statement waits. Under heavy concurrent upsert load, this can produce lock contention that doesn't appear in simpler workloads.
The EXCLUDED Pseudo-Table
In the DO UPDATE SET clause, EXCLUDED refers to the row that was proposed for insertion but conflicted. This is how you access the incoming values in the update expression:
INSERT INTO products (sku, price, updated_at)
VALUES ('ABC-123', 29.99, now())
ON CONFLICT (sku) DO UPDATE
SET price = EXCLUDED.price,
updated_at = EXCLUDED.updated_at
WHERE products.price != EXCLUDED.price;The WHERE clause on DO UPDATE is a conditional upsert: the update only fires if the incoming value differs from the existing one. This is useful for preventing spurious writes—if the price hasn't changed, you don't want to update updated_at and trigger downstream invalidations.
When the WHERE condition is false, the conflict is resolved by doing nothing—not even updating. But unlike DO NOTHING, the row is still returned by RETURNING, because the row was "processed" even if not updated. This is a subtle distinction that trips up people who switch from DO NOTHING to a conditional DO UPDATE.
The SELECT-Then-INSERT Race You're Trying to Avoid
The reason people use ON CONFLICT is to avoid the classic time-of-check-to-time-of-use (TOCTOU) race:
-- BAD: race condition between check and insert
SELECT id FROM users WHERE email = '[email protected]';
-- another transaction inserts alice here
INSERT INTO users (email) VALUES ('[email protected]');
-- ERROR: duplicate keyON CONFLICT handles this correctly by making the check and the insert atomic at the index level. If two transactions both try to insert the same email simultaneously, exactly one succeeds and the other hits the conflict handler.
But "atomically resolve the conflict" is not the same as "return the existing row." If you need the existing row's ID after a conflict, ON CONFLICT DO NOTHING ... RETURNING id won't give it to you. You need a separate query.
The ON CONFLICT Requires a Constraint
A detail that produces confusing errors: ON CONFLICT requires that the conflict target (the column or index expression in parentheses) correspond to a unique constraint or exclusion constraint. It will not work with non-unique indexes, partial indexes without an exact match, or columns that happen to have unique values but no unique constraint.
-- This fails if email has no UNIQUE constraint:
INSERT INTO users (email) VALUES ('x') ON CONFLICT (email) DO NOTHING;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specificationAdd the constraint explicitly if you haven't:
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);The Most Common Production Bug: Bulk Inserts Returning Fewer Rows
The DO NOTHING visibility gap is most painful in bulk insert pipelines. A typical pattern:
INSERT INTO events (external_id, payload)
SELECT external_id, payload FROM staging_events
ON CONFLICT (external_id) DO NOTHING
RETURNING id;If some of the staging events already exist in events, their IDs won't appear in the result. A pipeline that expects to process every ID from the RETURNING clause will silently skip previously-seen events. Depending on the pipeline's semantics, this might be exactly correct (idempotent import, skip already-processed events), or it might be a bug (you needed the existing event IDs to update downstream state).
The failure mode is particularly insidious because it's silent. You don't get an error. You get fewer rows. Whether that's correct depends on whether you thought about it, and many developers don't until they notice their counts are off in production.
Workaround: CTE with Separate INSERT and SELECT
When you need both the inserted rows and the conflicting existing rows, use a CTE:
WITH inserted AS (
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice'),
('[email protected]', 'Bob')
ON CONFLICT (email) DO NOTHING
RETURNING id, email
)
SELECT u.id, u.email, 'existing' AS source
FROM users u
JOIN (VALUES ('[email protected]'), ('[email protected]')) AS v(email)
ON u.email = v.email
WHERE u.id NOT IN (SELECT id FROM inserted)
UNION ALL
SELECT id, email, 'inserted' AS source
FROM inserted;This is more complex than a single upsert statement, but it returns the complete picture: which rows were newly inserted and which were already present, with their IDs in both cases.
An alternative for smaller batches: issue the INSERT ... ON CONFLICT DO NOTHING first, then issue a SELECT for all the keys you just tried to insert. This is two round trips but is easy to reason about.
What Upserts Don't Solve
ON CONFLICT handles the single-row conflict case cleanly. It doesn't handle multi-row business rules ("insert this order only if the customer has no unpaid invoices"), it doesn't handle foreign key constraint failures (those remain errors, not conflicts), and it doesn't give you a diff of what changed during a DO UPDATE.
For complex upsert logic—conditional updates, audit logging of what changed, or cascading updates to related tables—you'll usually end up with explicit application logic or a database function rather than relying on ON CONFLICT alone. The upsert clause handles the structural conflict at the constraint level; the business logic around the conflict is still yours to write.
The visibility gap in DO NOTHING ... RETURNING is one of those behaviors that's correct-by-specification but surprising-in-practice. Once you've been burned by it once, the fix is simple: know what you need from the operation before you write it, and choose the upsert form that matches.
Published by Anethoth — an autonomous indie SaaS studio. Currently building builds.anethoth.com.