INSERT ... ON CONFLICT ... RETURNING gives back no row for the existing record
An empty RETURNING result when the row already existsShort answer
RETURNING only returns rows that were actually inserted or updated. A row skipped by DO NOTHING, or left alone because the DO UPDATE WHERE condition was false, is not returned. Use DO UPDATE with a harmless SET so the row counts as updated, or look the row up in a second query.
Fix it fast
1Find out which cause you have
Run the statement twice with the same key. The first call returns the row; if the second returns nothing, you are in the cases below.
INSERT INTO users (email) VALUES ('a@example.com') ON CONFLICT (email) DO NOTHING RETURNING id;2Match what you see, then jump to its fix
- 1
The first call returns the id, the repeat call returns no row.DO NOTHING skips the row, so nothing is returnedFix - 2
You wrote DO UPDATE ... WHERE and the row exists but is not returned.A WHERE condition on DO UPDATE is falseFix - 3
A multi-row INSERT with DO UPDATE fails with a cardinality violation error.The same key appears twice in one multi-row insertFix
- 1
What does the error look like?
An empty RETURNING result when the row already existsApplication code gets no id back on the second call for the same key
Cause 1 of 3
DO NOTHING skips the row, so nothing is returned
1Check
The RETURNING clause computes values for each row actually inserted or updated, and only those. DO NOTHING avoids the insert, so there is no row to return.
SELECT id FROM users WHERE email = 'a@example.com';2Fix
Turn the conflict into an update that changes nothing real. The row is then updated and comes back through RETURNING.
INSERT INTO users (email) VALUES ('a@example.com')
ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email
RETURNING id;3Verify
Run it twice. Both calls return the same id.
SELECT count(*) FROM users WHERE email = 'a@example.com';Confirmed by postgresql.org
Cause 2 of 3
A WHERE condition on DO UPDATE is false
1Check
The row is locked but not updated when the condition is not satisfied, and the documentation says such a row is not returned.
grep -rn "ON CONFLICT" ./migrations ./src2Fix
Remove the WHERE if you always want the row back, or fetch the row separately when the condition fails.
INSERT INTO counters (key, hits) VALUES ('home', 1)
ON CONFLICT (key) DO UPDATE SET hits = counters.hits + 1
RETURNING key, hits;3Verify
Each call returns the row.
SELECT * FROM counters WHERE key = 'home';Confirmed by postgresql.org
Cause 3 of 3
The same key appears twice in one multi-row insert
1Check
A DO UPDATE statement may not affect any single existing row more than once, so rows proposed for insertion must not duplicate each other on the arbiter columns.
SELECT email, count(*) FROM staging_users GROUP BY email HAVING count(*) > 1;2Fix
Remove duplicates from the batch before the insert, for example with DISTINCT ON, then run the upsert.
INSERT INTO users (email, name)
SELECT DISTINCT ON (email) email, name FROM staging_users ORDER BY email
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING id;3Verify
The statement completes and returns one row per distinct email.
SELECT count(DISTINCT email) FROM staging_users;Confirmed by postgresql.org
How do I stop it coming back?
- Decide per query whether you need the row back; if you do, use DO UPDATE and not DO NOTHING.
- Deduplicate batch input on the conflict columns before the statement runs.
- Remember a no-op DO UPDATE still counts as an update, so use it only where that is acceptable.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
INSERT ... ON CONFLICT fails because no unique constraint matches the conflict target
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
PostgreSQL must find a unique index that covers exactly the columns in your ON CONFLICT list.
4 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix