Technology · PRACTICAL GUIDE
PostgreSQL duplicate key on a generated ID? Prove sequence drift before setval
Read the violated constraint, identify who generated the key, resolve the owned sequence from the catalog, and stop writers before one targeted repair. A business-key duplicate or live race needs a different fix.
A PostgreSQL duplicate-key error proves that one constrained value already exists. It does not prove the primary-key sequence is wrong.
Preserve the constraint and rejected value, identify whether the database or application generated it, then compare the catalog-resolved sequence with the table under controlled writer conditions.
Preserve the exact constraint and rejected value
Save the full error, especially the constraint name and DETAIL key/value. A duplicate email or SKU is an application or source-data collision; resetting an ID sequence cannot fix it.
Record the failing statement shape, whether the ID was omitted or supplied explicitly, the schema-qualified table, and what changed before the first failure: import, seed, restore, failover, replication or manual insert.
Prove which mechanism generates the column
Inspect the column default or identity definition. Ask the catalog with pg_get_serial_sequence('schema.table','id') instead of constructing a sequence name; the function supports owned serial and identity sequences and returns NULL when no sequence is associated.
If the application supplies the key, the default is not invoked. If the constraint covers several columns or a business key, stop the sequence branch and investigate retries, idempotency and conflicting source records.
Compare table and sequence state without advancing it
Read MAX(id) from the table and inspect the resolved sequence's last_value and is_called. Do not call nextval merely to inspect it: nextval advances atomically and its value is not reclaimed after an aborted transaction.
Check start, increment, minimum, maximum and cycle settings before using a recipe built for a positive increment of one. An empty table, renamed table, unowned sequence, descending sequence or partitioned design needs its own explicit decision.
Quiesce writers before a targeted repair
Use a maintenance window or otherwise stop application inserts and loaders that can race with MAX(id). PostgreSQL documents that setval becomes visible immediately and is not undone by rollback, so a transaction wrapper is not a substitute for controlling concurrent writers.
For a proven nonempty, increment-by-one owned sequence, set its current value to the table maximum with is_called true. For an empty table, choose the documented start value and is_called false, or use an appropriate identity restart. Never use TRUNCATE RESTART IDENTITY unless deleting the table's rows is actually intended.
Verify one generated insert and close the root cause
Restore one writer and perform a controlled insert that omits the generated column. Confirm the new key is unique, the intended row committed, and the sequence now moves in the expected direction. A gap is not evidence of corruption; PostgreSQL sequences are not gapless.
Then fix the cause: stop seed/import code from supplying generated IDs unless required, add an explicit post-load sequence reconciliation step, verify failover or logical-replication behavior, and alert on a generated value colliding with an existing primary key. If drift returns, do not keep pushing setval forward without finding the writer.
