ON CONFLICT still raises a duplicate key error when a table has several unique constraints
ERROR: duplicate key value violates unique constraintShort answer
ON CONFLICT only handles the constraint you name as its conflict target. A row that breaks a different unique constraint still raises the normal duplicate key error. Use DO NOTHING without a target to cover all of them, or split the logic, and never send two rows that clash with each other.
Fix it fast
1Find out which cause you have
List every unique constraint and unique index on the table, so you know which ones can fire.
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users' AND indexdef LIKE '%UNIQUE%';2Match what you see, then jump to its fix
- 1
The error names a constraint that is not the one in your ON CONFLICT (...) clauseThe conflict target names only one of several constraintsFix - 2
You want an update on conflict, but the table has two keys that can clashDO UPDATE needs a target, and a target covers only one indexFix - 3
A multi-row INSERT with DO UPDATE fails although the table started emptyTwo rows in the same INSERT clash with each otherFix
- 1
What does the error look like?
ERROR: duplicate key value violates unique constraintduplicate key value violates unique constraint, even though ON CONFLICT is in the INSERT
Cause 1 of 3
The conflict target names only one of several constraints
1Check
With a conflict target, only the matching unique index (the arbiter) triggers the alternative action. A violation of any other unique constraint is raised as an error.
-- fails on users_email_key although the target is (username)
INSERT INTO users (username, email) VALUES ('ann', 'ann@example.com')
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;2Fix
If you only want to skip rows that clash with anything, drop the target: ON CONFLICT DO NOTHING handles all usable constraints and unique indexes.
INSERT INTO users (username, email) VALUES ('ann', 'ann@example.com')
ON CONFLICT DO NOTHING;3Verify
Run the same insert twice; the second run reports INSERT 0 0 and no error.
SELECT count(*) FROM users WHERE username = 'ann';Confirmed by postgresql.org
Cause 2 of 3
DO UPDATE needs a target, and a target covers only one index
1Check
For DO UPDATE the conflict target is required, so you must choose one key to update on. The other key is not protected by the clause.
SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'users'::regclass AND contype IN ('u','p');2Fix
Pick the key that identifies the row, upsert on it, and decide separately what a clash on the second key should mean. For example, update on the key you trust and let the other raise, so the application sees a real data conflict.
INSERT INTO users (username, email) VALUES ('ann', 'ann@example.com')
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;3Verify
Insert a second user with the same email under a new username and confirm the error is reported, not hidden.
SELECT username, email FROM users ORDER BY username;Confirmed by postgresql.org
Cause 3 of 3
Two rows in the same INSERT clash with each other
1Check
INSERT with DO UPDATE may not affect any single row more than once, so rows in one statement must not share a value of the arbiter key.
SELECT username, count(*) FROM staging_users GROUP BY username HAVING count(*) > 1;2Fix
Remove the duplicates from the input first, keeping one row per key.
INSERT INTO users (username, email)
SELECT DISTINCT ON (username) username, email
FROM staging_users ORDER BY username, created_at DESC
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;3Verify
The statement runs and the table holds one row per username.
SELECT username, count(*) FROM users GROUP BY username HAVING count(*) > 1;Confirmed by postgresql.org
How do I stop it coming back?
- Decide which key is the identity of the row and use only it as the conflict target.
- Deduplicate batch input before the INSERT.
- Keep the number of unique constraints on a table small and documented.
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