Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

ON CONFLICT still raises a duplicate key error when a table has several unique constraints

ErrorERROR: duplicate key value violates unique constraint

Short 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.

  • Applies to PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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%';
  2. 2Match what you see, then jump to its fix

    1. 1The error names a constraint that is not the one in your ON CONFLICT (...) clauseThe conflict target names only one of several constraintsFix
    2. 2You 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. 3A multi-row INSERT with DO UPDATE fails although the table started emptyTwo rows in the same INSERT clash with each otherFix

What does the error look like?

  • ERROR: duplicate key value violates unique constraint
  • duplicate 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.