PostgreSQL "duplicate key value violates unique constraint": causes and fixes

An insert that has always worked suddenly fails:

ERROR:  duplicate key value violates unique constraint "departments_pkey"
DETAIL:  Key (id)=(7) already exists.

PostgreSQL refuses to store a second row with the same value in a column (or set of columns) that must be unique. There are two very different reasons this happens, and the fix depends on which one you have:

1. The data really is a duplicate — you’re inserting an email, a username or an order number that already exists.
2. The sequence is out of sync — you’re not choosing the id at all, PostgreSQL is, and it picks one that’s already taken. This one typically appears right after a data import, a migration or a restore.

The DETAIL line tells you which: if the key is an id you never typed, it’s almost always the sequence.

Every SQL example below runs as-is in the SQL Playground, on a real PostgreSQL database.

TL;DR

-- Sequence out of sync: move it past the highest id
SELECT setval(pg_get_serial_sequence('departments', 'id'),
              (SELECT COALESCE(max(id), 0) + 1 FROM departments), false);

-- Real duplicate: insert, or update the existing row instead
INSERT INTO employees (...) VALUES (...)
ON CONFLICT (email) DO UPDATE SET job_title = EXCLUDED.job_title;

Cause 1: the sequence is out of sync

Auto-generated ids (serial or GENERATED ... AS IDENTITY columns) come from a sequence — a counter that hands out the next number. The sequence doesn’t look at the table. If rows are inserted with explicit ids — an import script, a CSV load, a copy from another database — the counter isn’t moved forward, and sooner or later it hands out an id that already exists.

Reproduce it

The departments table has ids 1–6 and its sequence is ready to give out 7. Now an “import” adds two rows with explicit ids:

INSERT INTO departments (id, name, location) VALUES (7, 'Legal', 'Lisbon'), (8, 'Support', 'Madrid');

The next normal insert asks the sequence for an id, gets 7 — and fails:

-- ERROR: duplicate key value violates unique constraint "departments_pkey"
INSERT INTO departments (name, location) VALUES ('Security', 'Remote');

Run it again and it fails with id 8; a third time and it works with 9. A sequence never gives the same number twice, even when the insert fails — which is why “it works if you retry” is a classic symptom of this problem.

Confirm it

Compare the highest id in the table with the sequence’s current value:

SELECT (SELECT max(id) FROM departments) AS max_id,
       pg_get_serial_sequence('departments', 'id') AS sequence_name;

SELECT last_value, is_called FROM departments_id_seq;

If last_value is lower than max_id, the sequence is behind.

Fix it

Move the sequence past the highest id. pg_get_serial_sequence finds the sequence name for you, and the false argument means “the next value returned will be exactly this number”:

SELECT setval(pg_get_serial_sequence('departments', 'id'),
              (SELECT COALESCE(max(id), 0) + 1 FROM departments), false);
INSERT INTO departments (name, location) VALUES ('Security', 'Remote') RETURNING id;

COALESCE(…, 0) makes it work on an empty table too.

Fix every table after an import

After a bulk import, several tables are usually affected. This query generates one setval statement per auto-generated column in the public schema — review the output, then run it:

SELECT format(
  'SELECT setval(%L, (SELECT COALESCE(max(%I), 0) + 1 FROM %I.%I), false);',
  pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name),
  column_name, table_schema, table_name
) AS fix
FROM information_schema.columns
WHERE table_schema = 'public'
  AND (column_default LIKE 'nextval(%' OR is_identity = 'YES');

In psql, add \gexec after the query (instead of ;) to run every generated statement directly.

Cause 2: the data really is a duplicate

Here the key in DETAIL is a value you (or your users) provided:

-- ERROR: duplicate key value violates unique constraint "employees_email_key"
INSERT INTO employees (first_name, last_name, email, job_title, salary, hired_on)
VALUES ('Emma', 'Clarke', 'emma.clarke@example.com', 'CTO', 9800, '2026-11-01');

The constraint is doing its job. What to do depends on what the insert was meant to do.

Update the existing row instead (upsert)

ON CONFLICT turns “insert” into “insert, or update if it already exists” — in one atomic statement:

INSERT INTO employees (first_name, last_name, email, job_title, salary, hired_on)
VALUES ('Emma', 'Clarke', 'emma.clarke@example.com', 'Chief Technology Officer', 9900, '2018-02-01')
ON CONFLICT (email) DO UPDATE
SET job_title = EXCLUDED.job_title, salary = EXCLUDED.salary
RETURNING id, email, job_title, salary;

EXCLUDED is the row you tried to insert. To simply skip duplicates, use ON CONFLICT (email) DO NOTHING.

Find duplicates before adding a unique constraint

The same error appears when you try to add a unique constraint or index to a column that already contains duplicates. Find them first:

SELECT lower(email) AS email, count(*)
FROM employees
GROUP BY lower(email)
HAVING count(*) > 1;

No rows means it’s safe to add the constraint. The lower() also catches duplicates that differ only in letter case.

Gaps in ids are normal

Failed inserts, rolled-back transactions and ON CONFLICT all use up sequence values, so ids will have gaps (1, 2, 5, 9…). That’s by design and not a problem — never “fix” gaps by reusing old ids.