🚨 PostgreSQL Error Guide: 'duplicate key value violates unique constraint' Unique Index Conflicts

Exact Error Message

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(jane@example.com) already exists.

The SQLSTATE is 23505 (unique_violation).

What the Error Means

PostgreSQL enforces uniqueness with unique indexes, created by UNIQUE constraints or PRIMARY KEY declarations. When an INSERT or UPDATE would produce a row whose indexed column(s) duplicate an existing row, PostgreSQL rejects it with duplicate key value violates unique constraint. The DETAIL line names the conflicting column(s) and the exact value that collided, and the constraint name in the message tells you which unique index was violated.

This is a correctness guarantee working as designed: the database is preventing two rows from sharing a value that must be unique. The error has two broad flavors. The benign flavor is an application that genuinely tried to insert a value that already exists (a duplicate signup, a re-run import) — here the fix is in the application’s logic, often via UPSERT. The pathological flavor is a primary key collision caused by a sequence that has fallen out of sync with the table’s actual maximum id, which signals a deeper data-management problem.

Common Causes

• Genuine duplicate data. The application inserts a value (email, username, external id) that already exists — duplicate form submissions, retries, or re-imported data.
• Race conditions on check-then-insert. Two concurrent requests both check that a value is absent, then both insert it; one succeeds and the other violates the constraint.
• Out-of-sync sequence on a primary key. After a bulk load with explicit ids, or a restored dump, the sequence is behind the table’s max id, so the next generated id collides.
• Reused or hard-coded ids. Inserts that supply explicit primary key values overlapping existing rows.
• Logical replication / multi-master conflicts. Two nodes generate the same key and conflict on merge.

How to Reproduce the Error

Create a table with a unique constraint and insert the same value twice:

CREATE TABLE users (id serial PRIMARY KEY, email text UNIQUE);
INSERT INTO users (email) VALUES ('jane@example.com');
INSERT INTO users (email) VALUES ('jane@example.com');
-- ERROR:  duplicate key value violates unique constraint "users_email_key"
-- DETAIL:  Key (email)=(jane@example.com) already exists.

To reproduce the sequence-drift variant, insert a row with an explicit id ahead of the sequence, then let the sequence catch up:

INSERT INTO users (id, email) VALUES (1000, 'bulk@example.com');
-- later, serial generates id 1..N and eventually reaches 1000:
INSERT INTO users (email) VALUES ('next@example.com');  -- collides at id 1000

Diagnostic Commands

Inspect the unique constraint definition (read-only):

psql -c "SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint \
WHERE conrelid = 'users'::regclass AND contype IN ('u','p');"

Find the existing row that owns the conflicting value:

psql -c "SELECT id, email FROM users WHERE email = 'jane@example.com';"

Check whether the primary key sequence is behind the table’s max id:

psql -c "SELECT max(id) AS max_id, (SELECT last_value FROM users_id_seq) AS seq_last FROM users;"

List columns covered by the unique index:

psql -c "SELECT a.attname FROM pg_index i \
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) \
WHERE i.indrelid = 'users'::regclass AND i.indisunique;"

Look for the violation frequency in the log:

sudo journalctl -u postgresql --no-pager | grep "duplicate key value" | tail -20

Step-by-Step Resolution

1. Read the DETAIL line. It names the exact column(s) and value that collided and the constraint. This immediately tells you whether it is a business-data duplicate or a key collision.

2. For genuine duplicates, decide insert vs. update. Use INSERT ... ON CONFLICT to handle the conflict gracefully — either DO NOTHING to skip, or DO UPDATE to upsert:

   INSERT INTO users (email, name) VALUES ('jane@example.com', 'Jane')
   ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

3. For race conditions, rely on the constraint, not check-then-insert. Do not pre-check existence in application code; attempt the insert and catch SQLSTATE 23505, or use ON CONFLICT. The unique index is the only race-free arbiter.

4. For sequence drift, resynchronize the sequence. If a primary key sequence is behind the table’s max id, reset it so future ids do not collide:

   SELECT setval('users_id_seq', (SELECT max(id) FROM users));

   This is the standard fix after bulk loads or dump restores that inserted explicit ids.

5. Clean up duplicate data if it already exists. If duplicates slipped in before the constraint was added, identify and merge/remove them before (re)creating the unique index.

6. Verify. Re-run the insert path and confirm it now succeeds or upserts cleanly, and that the sequence’s last_value is at or above max(id).

Prevention and Best Practices

• Let the database enforce uniqueness; never replace a unique constraint with application-level check-then-insert, which races under concurrency.
• Use INSERT ... ON CONFLICT (UPSERT) for idempotent inserts so retries and duplicates are handled gracefully.
• After bulk loads or pg_restore with explicit ids, always setval the relevant sequences to the table maximum.
• Avoid supplying explicit primary key values in normal application paths; let sequences/identity columns own them.
• Catch SQLSTATE 23505 in application code and translate it into a meaningful response (for example, “email already registered”).
• For triage, the free incident assistant can turn a unique-violation log block into a sequence-drift or race-condition diagnosis.

Related Errors

• duplicate key value violates unique constraint "..._pkey" — the same error on a primary key, often sequence drift.
• null value in column ... violates not-null constraint (SQLSTATE 23502) — a different integrity violation.
• insert or update on table ... violates foreign key constraint (SQLSTATE 23503) — referential integrity, not uniqueness.
• value too long for type character varying(n) — a length check, unrelated to uniqueness.

Frequently Asked Questions

How do I avoid this when two requests insert at the same time? Do not check for existence first and then insert — that is a race. Instead, attempt the insert and either catch SQLSTATE 23505 or use INSERT ... ON CONFLICT DO NOTHING/DO UPDATE. The unique index resolves the race atomically; only one of the concurrent inserts wins.

My primary key keeps colliding after a data import. What happened? The import inserted rows with explicit id values, but the sequence backing the column was not advanced. When the sequence later generates ids, it collides with the imported ones. Fix it with SELECT setval('table_id_seq', (SELECT max(id) FROM table));.

Should I use ON CONFLICT DO NOTHING or DO UPDATE? Use DO NOTHING when a duplicate should simply be ignored (idempotent inserts). Use DO UPDATE (UPSERT) when an existing row should be updated with new values. EXCLUDED references the row you tried to insert.

Can I find which existing row caused the conflict? Yes. The DETAIL line gives the conflicting value; query the table on the unique column(s) with that value to see the existing row. This helps decide whether to merge, update, or reject the new data.

Does adding ON CONFLICT hide real data problems? It can if used carelessly. UPSERT is right for idempotency, but if duplicates indicate a bug (for example, sequence drift), silently swallowing them masks it. Make sure the conflict is genuinely expected before suppressing it.