"duplicate key value violates unique constraint" on INSERT with a SERIAL column: the sequence that fell behind

verbatim errorERROR: duplicate key value violates unique constraint "orders_pkey" DETAIL: Key (id)=(1042) already exists. # on a plain INSERT INTO orders (name) VALUES ('x') -- id comes from the sequence

Problem

Every new row insert on one table failed, and had been for days:

ERROR: duplicate key value violates unique constraint "orders_pkey"
DETAIL: Key (id)=(1042) already exists.
# on a plain INSERT INTO orders (name) VALUES ('x') -- id comes from the sequence

The sequence's nextval was returning ids that already existed. New inserts could not succeed until the sequence was corrected; existing data was fine.

Root cause

Sequences and table data are independent state. Something moved rows without advancing the sequence:

Data imported/restored with explicit ids (INSERT ... (id, ...) VALUES (1042, ...) — a pg_dump --data-only followed by re-import, or a CSV import with ids) leaves the sequence wherever it was.
A replication/copy from another environment copies rows but not sequence state.
The bigserial→bigint GENERATED BY DEFAULT AS IDENTITY migration where backfill inserted explicit ids.

GENERATED ALWAYS AS IDENTITY would have rejected the explicit-id inserts that caused this; SERIAL/BY DEFAULT allows them silently. That permission is exactly the bug's opening.

fix preview — first 2 of 3 lines (sql), truncated:

SELECT last_value, (SELECT max(id) FROM orders) AS max_id
FROM orders_id_seq;
… 1 more line in the fix

🔒 the fix — including 3 code blocks — is members-only. $1/mo unlocks everything.