An insert that worked yesterday now fails, often right after a data import, a restore, or a deploy that seeds fixtures. On PostgreSQL 17 (16 prints the same):
ERROR: duplicate key value violates unique constraint "products_pkey"
DETAIL: Key (id)=(5) already exists.A unique index (here the primary key index products_pkey) already holds the value your row tries to insert, so PostgreSQL rejects the whole statement. The SQLSTATE is 23505 (unique_violation). The DETAIL line is the most useful part: it names the column and the value that collided. It is left out when your role can’t SELECT the key columns.
Quick fix checklist
- Read the constraint name.
<table>_pkeyon an id you didn’t supply points to a sequence that is behind.<table>_<column>_keyor a custom index name points to genuinely duplicate business data. - Compare the sequence with the data:
SELECT max(id) FROM products;versusSELECT last_value FROM products_id_seq;. - Resync it:
SELECT setval(pg_get_serial_sequence('products', 'id'), coalesce(max(id), 0) + 1, false) FROM products; - Find what inserted explicit ids (a CSV import, a fixture, a copy from another database) and fix that process.
- For “create if missing”, replace
SELECTfollowed byINSERTwithINSERT ... ON CONFLICT. - Inside a transaction, the error aborts the transaction. Roll back (or use a savepoint) before retrying.
Before you start
You need psql or another SQL client connected to the affected database as a role that can read the table and its sequence, plus the full error, including the DETAIL line (ORMs often wrap it; look for detail or constraint on the driver’s error object). The examples were run on PostgreSQL 17.5 and apply unchanged to 16.
Why it happens
A unique constraint is enforced by a unique B-tree index. Every insert, and every update that changes the key, probes that index. If a live row already has the key, the statement fails. If another transaction has inserted the same key but not yet committed, the insert waits for that transaction: it fails if the other one commits and succeeds if it rolls back. The constraint is the one component that sees every concurrent writer, which is why it’s the right place to enforce uniqueness.
The error has three common sources:
- A sequence out of sync.
serialand identity columns take their default from a sequence vianextval(). A sequence is a counter that knows nothing about the table. When rows are inserted with explicit ids (INSERT ... (id, ...),COPYfrom a CSV that includes the id, fixtures, a data-only copy between environments), the table moves ahead and the sequence doesn’t. The next default id is one that already exists. Logical replication in PostgreSQL 16 and 17 also doesn’t carry sequence values, so a promoted subscriber hits this on its first insert. - Real duplicate data. Two sign-ups with the same email, or the same order imported twice. Here the constraint is doing its job.
- A race. Application code checks
SELECT ... WHERE email = $1, sees nothing, then inserts. Two requests can both pass the check before either inserts. The second insert then waits on the unique index and fails once the first commits. Raising the isolation level doesn’t remove the race. UnderSERIALIZABLEyou may get a serialization failure instead, which also has to be handled.
GENERATED BY DEFAULT AS IDENTITY behaves like serial here: it accepts explicit ids and its sequence doesn’t move. GENERATED ALWAYS AS IDENTITY rejects explicit ids unless you write OVERRIDING SYSTEM VALUE, which makes accidental desynchronisation much harder.
Step-by-step walkthrough
Step 1: Reproduce the sequence drift
CREATE TABLE products (
id serial PRIMARY KEY,
sku text NOT NULL UNIQUE,
name text NOT NULL
);
-- the app inserts normally: ids 1..4 from the sequence
INSERT INTO products (sku, name)
VALUES ('A1','Mug'), ('A2','Cap'), ('A3','Pen'), ('A4','Bag');
-- an import brings its own ids
INSERT INTO products (id, sku, name)
VALUES (5,'B1','Lamp'), (6,'B2','Desk'), (7,'B3','Chair');
-- the app inserts again
INSERT INTO products (sku, name) VALUES ('C1', 'Tray');The last statement fails with Key (id)=(5) already exists. The sequence handed out 5 because it last gave out 4.
Step 2: Compare the sequence with the table
pg_get_serial_sequence finds the sequence behind a serial or identity column, so you don’t have to guess its name:
SELECT pg_get_serial_sequence('products', 'id');
-- public.products_id_seq
SELECT (SELECT max(id) FROM products) AS max_id,
(SELECT last_value FROM products_id_seq) AS seq_last; max_id | seq_last
--------+----------
7 | 5seq_last is 5, not 4, because the failed insert still consumed 5. Sequences are deliberately non-transactional, so failed inserts, rollbacks and ON CONFLICT attempts all use up values, and gaps in ids are normal.
Step 3: Resync the sequence
SELECT setval(pg_get_serial_sequence('products', 'id'),
coalesce(max(id), 0) + 1,
false)
FROM products;The third argument false means “the next nextval() returns exactly this value”. Combined with coalesce(..., 0) + 1, the statement also works on an empty table, where setval(seq, 0) would fail with setval: value 0 is out of bounds for sequence. The next insert now gets id 8. For identity columns, the same setval works, or use ALTER TABLE products ALTER COLUMN id RESTART WITH 8;.
If many tables were imported, generate one setval per table from information_schema.columns (filtering on column_default LIKE 'nextval%' or is_identity = 'YES') and run them in the same maintenance step.
Step 4: Handle real duplicates with ON CONFLICT
When the collision is on business data, decide what a duplicate should mean and say so in SQL:
-- "create if missing": a duplicate is not an error
INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email) DO NOTHING
RETURNING id;
-- "insert or update": last write wins for name
INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING id;DO NOTHING ... RETURNING returns no row when the email already existed, so the caller can tell the two outcomes apart. The conflict target must match a unique index or constraint, otherwise you get there is no unique or exclusion constraint matching the ON CONFLICT specification. A multi-row insert that contains the same key twice fails with ON CONFLICT DO UPDATE command cannot affect row a second time, so deduplicate the batch first. MERGE (PostgreSQL 15+) isn’t a concurrency-safe substitute: it can still raise a unique violation when a matching row is inserted concurrently.
Step 5: Prevent it
- Prefer
GENERATED ALWAYS AS IDENTITYfor new tables, so stray explicit ids fail loudly instead of drifting silently. - Make every import or environment copy end with the
setvalstep. A fullpg_dumpalready includessetvalcalls; partial copies and CSV loads don’t. - In application code, catch SQLSTATE
23505at the boundary that knows what a duplicate means, and map it to a 409 or a friendly “already registered” message, not a 500.
Worked scenario
A team refreshes staging by recreating the schema from migrations and then loading the orders table from a production export with \copy orders FROM 'orders.csv' CSV HEADER. The CSV includes the id column. The first checkout on staging fails:
ERROR: duplicate key value violates unique constraint "orders_pkey"
DETAIL: Key (id)=(1) already exists.Diagnosis. The id is 1, which shows the sequence is at its starting point. \copy inserted 48,213 rows with their production ids and never touched orders_id_seq. SELECT max(id) FROM orders returns 48213, while last_value is 1.
Fix. Add the resync to the refresh script, right after the load:
psql "$STAGING_URL" -v ON_ERROR_STOP=1 <<'SQL'
\copy orders FROM 'orders.csv' CSV HEADER
SELECT setval(pg_get_serial_sequence('orders', 'id'),
coalesce(max(id), 0) + 1, false)
FROM orders;
SQLThe script now fails fast if the load fails, and leaves the sequence one past the highest imported id.
Common mistake
Retrying the insert until it works. Each failed attempt consumes a sequence value, so a retry loop eventually gets past the imported block. That only works when the gap is small, it hides the cause, and the next import breaks things again.
Dropping or disabling the unique constraint. The error goes away and duplicates start accumulating. Restoring the constraint later fails until someone cleans them up by hand.
Check-then-insert with a bigger lock or a higher isolation level. SELECT ... FOR UPDATE can’t lock a row that doesn’t exist yet. Wrapping the check in SERIALIZABLE turns some failures into serialization errors, and your code still has to retry those. The unique index plus ON CONFLICT is simpler and correct.
Running setval(seq, max(id)) without the false argument on an empty table. max(id) is NULL there, and the call sets nothing useful. The coalesce(..., 0) + 1, false form handles both cases.
Verify the behavior
After the resync, the sequence must be ahead of the data:
SELECT (SELECT max(id) FROM products) AS max_id,
nextval('products_id_seq') AS next_id;next_id must be greater than max_id (this check consumes one value, which is harmless). Then insert through the application path and confirm it succeeds. For the race, write a test that fires two concurrent “register this email” requests at the endpoint: exactly one row must exist afterwards, and both requests must get a non-500 response.
Interview exercise
“Our signup handler runs SELECT 1 FROM users WHERE email = $1, and inserts if no row comes back. Under load we still see duplicate key value violates unique constraint "users_email_key". Why, and how would you fix it?”
Answer and reasoning
Two transactions can both run the SELECT before either inserts, and both see no row, because neither insert exists yet. Both then insert. The second insert finds the first one’s uncommitted entry in the unique index and waits. When the first commits, the second fails with 23505. Raising the isolation level doesn’t create a lock on a row that doesn’t exist, so it doesn’t remove the race.
The fix is to let the constraint arbitrate in one statement: INSERT ... ON CONFLICT (email) DO NOTHING RETURNING id. If a row comes back, the user is new. If not, the email was taken, and the handler can return 409 or fetch the existing row. Alternatively, keep the plain insert and translate SQLSTATE 23505 into the “already registered” response. Either way the unique index stays the source of truth, because it’s the only component that sees all concurrent writers.