SQL "duplicate key value violates unique constraint" (tested)

A duplicate key error means a write tried to put two rows with the same value into a column that has a UNIQUE or PRIMARY KEY constraint. PostgreSQL's wording is duplicate key value violates unique constraint; other databases use different text for the same rule. The fix depends on whether the duplicate was a mistake or expected, which section 3 below covers.

1. Reading the error

PostgreSQL gives two lines. The first names the constraint, the second shows the exact value that collided:

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

The constraint name tells you which column (or columns) is unique. Postgres auto-names constraints as <table>_<column>_key for a plain UNIQUE column, or <table>_pkey for the primary key, unless you named it yourself when creating the table.

2. Two reproduced triggers

The third row matters: this is not only an INSERT problem. Any statement that changes a unique column's value — INSERT, UPDATE, or a MERGE/upsert — can hit it.

3. Decide: is the duplicate a bug, or expected?

Two different fixes, depending on which:

• It's a bug (e.g. you generated an ID wrong, or re-ran an import without clearing old rows first): fix the logic that produces the duplicate. The error is doing its job.
• It's expected (e.g. an import that re-runs and should skip or update rows that already exist): use ON CONFLICT, tested below.

4. Fix for expected duplicates: ON CONFLICT

Ignore the duplicate — keep the existing row, don't error:

psql> INSERT INTO users (email, name) VALUES ('a@x.com','Dup') ON CONFLICT DO NOTHING;
INSERT 0 0
psql> SELECT * FROM users;
 id |  email  | name
----+---------+-------
  1 | a@x.com | Alice
(1 row)

INSERT 0 0 means zero rows were inserted — no error, and the original row ("Alice") is untouched.

Upsert — update the existing row with the new values instead of erroring:

psql> INSERT INTO users (email, name) VALUES ('a@x.com','AliceUpdated')
      ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
INSERT 0 1
psql> SELECT * FROM users;
 id |  email  |     name
----+---------+--------------
  1 | a@x.com | AliceUpdated
(1 row)

EXCLUDED refers to the row that was proposed for insertion but conflicted — so EXCLUDED.name is the new value ("AliceUpdated"), not the old one. ON CONFLICT (email) must name the exact unique column (or constraint) that might collide; ON CONFLICT DO NOTHING without a column list catches any unique violation on the table.

5. Fix for a genuine bug: find where the duplicate comes from

• If it's an auto-increment/serial primary key, check whether something inserted a row with an explicit ID (as in row 2 of the table above) that collides with a future auto-generated value. Postgres's sequence doesn't know about manually-inserted IDs; reset it with SELECT setval('users_id_seq', (SELECT max(id) FROM users)); if this happens.
• If it's a business key (email, SKU, username), the duplicate is usually real data: a retry that didn't check first, or two code paths racing to insert the same thing. Check first with a SELECT, or use ON CONFLICT DO NOTHING as a safety net even when you believe you already checked — the check and the insert are not atomic otherwise.

6. Other databases (per their docs, not measured on this box)

The underlying rule (a unique constraint rejects a second row with the same value) is identical across all of them; only the wording and upsert syntax differ.

Written with AI assistance. Measured on 2026-10-03 with PostgreSQL 16.15 (official Docker image), using psql. Every error message below is copy-pasted output, not paraphrased. MySQL, SQLite and SQL Server enforce the same rule but phrase the error differently (per their docs, not measured here). PlainHeaders is our extension; PostgreSQL and Docker are free.