23505 🐘 PostgreSQL

PostgreSQL: ERROR: duplicate key value violates unique constraint

An INSERT/UPDATE would create a duplicate in a UNIQUE or PRIMARY KEY column — or the id sequence is behind the existing data.

Seen on: PostgreSQL

Meaning

The DETAIL line names the key: Key (email)=(a@b.com) already exists. If the key is id and you never set ids manually, the sequence is out of sync — very common after importing data or restoring a dump with explicit ids.

Common causes

  • Inserting a value that already exists (double submit, retry, race)
  • Sequence behind max(id) after a manual import/restore (Key (id)=(5) already exists)
  • Unique index on a column you didn’t expect (case-insensitive email index)
  • Upsert logic that checks-then-inserts without a transaction

⚡ Quick fix

  1. Read the DETAIL line to see which key and value collided
  2. For id collisions, resync the sequence (below)
  3. Use INSERT … ON CONFLICT … DO UPDATE / DO NOTHING for upserts
  4. Catch SQLSTATE 23505 and return a friendly “already exists” error

Detailed fix by platform

PostgreSQL

  1. Resync an id sequence after imports:
    sql
    SELECT setval(pg_get_serial_sequence('users', 'id'), COALESCE(MAX(id), 1)) FROM users;
  2. Upsert instead of check-then-insert:
    sql
    INSERT INTO users (email, name) VALUES ('a@b.com', 'Asha')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

How to diagnose

  1. Key — Which constraint and value (DETAIL line)?
  2. Sequence — Is it the id? Compare nextval with max(id)
  3. Concurrency — Two requests inserting the same value?

🧠 Still stuck? Analyze your error

Paste the full message, response headers or stack trace — we'll detect the platform and point to the most likely cause.