PostgreSQL "Duplicate Key Value Violates Unique Constraint"

An insert collided with a unique index. Postgres has a purpose-built answer (ON CONFLICT) — and for serial columns, a sneaky sequence-misalignment cause everyone hits once.

What you'll see

Root causes

Concurrent inserts of the same key (race)

Two sessions insert the same natural key simultaneously: check-then-insert has a TOCTOU gap. ON CONFLICT DO NOTHING / DO UPDATE makes the operation atomic.

Sequence behind the PK out of sync

Restores/migrations inserted explicit ids without advancing the sequence: nextval() hands out ids that already exist. SELECT last_value FROM <seq> vs MAX(id) shows the gap.

Fix it

  1. Identify the constraint and key that collided
    psql -c '\d+ <table>' | grep -A2 -i unique ; # error message names the exact value
  2. Sequence desync: advance the sequence past max(id)
    SELECT setval(pg_get_serial_sequence('<table>','id'), (SELECT MAX(id) FROM <table>));
  3. Races: use ON CONFLICT instead of check-then-insert
    INSERT INTO t (email, name) VALUES ($1,$2) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name RETURNING *;
  4. Prevent recurrence in migrations: always fix sequences after explicit-id loads
    # standard post-restore step: loop over serial columns and setval() them

Field note

ON CONFLICT needs a matching unique index/constraint — it can't target arbitrary conditions. Application-level 'SELECT then INSERT if missing' can never be made race-safe without locks; move it into the INSERT statement.

Common questions

Why would the PRIMARY KEY collide when I never set ids?

The sequence is behind the data — almost always after a restore or manual insert with explicit ids. setval() to MAX(id) fixes it permanently.

ON CONFLICT DO UPDATE vs DO NOTHING — which one?

DO NOTHING for 'insert if new, otherwise skip' (import dedup); DO UPDATE for upsert semantics (idempotent writes). Both make the check-and-write atomic under concurrency.

Related fixes

Ship it right the first time

Our most-documented failures, packaged as ready-to-ship starter kits: Docker, Kubernetes, and Terraform.

Browse the template store →

One-time. Yours to modify. Instant download from the NinjaOps template store.