Cookbook/ Data Integrity/ Duplicate Key From Out Of Sync...
Data Integrity · Runbook

Duplicate Key From Out Of Sync Sequence

Inserts into a recently restored or bulk-loaded table fail with 'ERROR: duplicate key value violates unique constraint "<table>_pkey"' and a DETAIL line...

🧩 5 steps ⏱ ~15 min 📊 Intermediate 🐘 PostgreSQL 14--18 Last reviewed June 26, 2026

In 10 seconds

A table that worked perfectly yesterday starts rejecting every new row this morning with 'duplicate key value violates unique constraint', and the duplicate it complains about is a tiny number -- id=1, then id=2 -- even though the table clearly already contains thousands of rows. Nothing in the application changed; what changed is that the table was reloaded. This is the classic out-of-sync-sequence outage, and it almost always follows a restore. When you load data with pg_restore --data-only, or with a COPY / INSERT that carries its own id column, the rows arrive with their original primary-key values -- but inserting an explicit value into a serial / identity column does NOT advance the column's owning sequence. The data lands at ids 1..1000 while the sequence is still parked at 1. The moment a normal INSERT (one that lets the DEFAULT supply the id) runs, it calls nextval, gets 1, and collides with the row that is already there -- SQLSTATE 23505, unique_violation on the primary key. Because the sequence is so far behind, it keeps handing back already-taken numbers, so every default-valued INSERT fails until the sequence is fast-forwarded past the loaded data. The lab makes this exact and deterministic: a table `accounts` with a serial primary key is loaded with explicit ids 1..1000 (the stand-in for a data-only restore) without touching its sequence, while a sibling table `orders_ok` is loaded the right way -- through the DEFAULT -- so its sequence advanced normally and stays in sync. A perfectly ordinary INSERT into accounts then raises 23505 'Key (id)=(1) already exists'. We read the FREE signal (the owning sequence sits far below max(id)), the PREMIUM signal (a fleet scan that joins every sequence to its column and flags EVERY one whose last value is below the column's real max), then the FIX (setval the sequence to max(id), after which inserts resume at 1001) and the PREVENTION (run a fleet-wide resync after any explicit-id load, and define keys as GENERATED ALWAYS AS IDENTITY so a careless explicit-id load is rejected outright instead of silently desyncing the sequence).

Why This Happens

A serial column is an int4/int8 column whose DEFAULT is nextval of an owned sequence, and a sequence is an independent object that only moves when nextval is called. Inserting an EXPLICIT value into the column bypasses the DEFAULT entirely -- nextval is never invoked -- so the sequence does not learn that id 1000 is now in use. This is by design and is normally harmless: pg_dump's own output finishes a table load with a setval that fast-forwards the sequence to max(id), which is why a full pg_dump/pg_restore round-trip leaves sequences correct.

The trouble comes from loads that carry data WITHOUT that setval: a --data-only restore (whose setval may be skipped or filtered), a COPY of just the rows, a manual INSERT ... SELECT that includes the id column, or an application 'seed' script. After such a load the sequence still points at its starting position while the data has marched ahead to max(id).

The next default-valued INSERT calls nextval, receives a value at or below max(id), and the primary-key unique index rejects it with errcode 23505 (ERRCODE_UNIQUE_VIOLATION), message 'duplicate key value violates unique constraint "..."' plus a DETAIL naming the exact colliding key. Note a subtle, honest detail the lab captures: nextval is non-transactional, so even a FAILED insert consumes a sequence value -- that is why the sequence reads last_value = 2 after two failed attempts rather than 1; it crept forward by the failures but is still nowhere near catching up to 1000.

The fix is a single, instantaneous setval: advance the sequence to the current max id so the next nextval returns a value beyond every loaded row. pg_get_serial_sequence('accounts','id') resolves the owning sequence by name without hardcoding it, and setval(seq, (SELECT max(id) FROM accounts)) sets last_value to 1000 with is_called true, so the next nextval returns 1001. There is no table rewrite, no lock of consequence, and no data change -- only the sequence's internal counter moves. The deeper prevention is structural. GENERATED ALWAYS AS IDENTITY makes the column refuse a raw explicit-id INSERT (errcode 428C9, 'cannot insert a non-DEFAULT value into column'), forcing a loader to either let the DEFAULT assign ids (keeping the sequence in step) or say OVERRIDING SYSTEM VALUE explicitly -- which is at least a deliberate, visible act rather than a silent desync. Either way, the class of bug where a restore quietly leaves the sequence behind its data is eliminated. Either way, the class of bug where a restore quietly leaves the sequence behind its data is eliminated.

DIAGNOSE

Diagnose

Free Tier -- Basic Approach

The on-call's first read with zero special tooling: take the table from the error and compare its owning sequence's current position to the highest id actually in the table. If the sequence is below max(id), it is doomed to keep generating duplicates.

SQL
SELECT last_value, is_called FROM accounts_id_seq;
OUTPUT -- captured on real PostgreSQL
last_value | is_called
------------+-----------
          2 | t
(1 row)
SQL
SELECT max(id) AS column_max_id FROM accounts;
OUTPUT -- captured on real PostgreSQL
column_max_id
---------------
          1000
(1 row)

The free diagnostic reads accounts_id_seq directly -- last_value = 2, is_called = t -- and puts it next to max(id) of accounts, which is 1000. The two numbers tell the whole story: the sequence is sitting at 2 while the data already reaches 1000, so the next default-valued INSERT will keep colliding until the sequence is advanced past the loaded rows.

(The sequence reads 2 rather than 1 because nextval is non-transactional: each of the two failed inserts in the break step still burned a value, nudging it forward without getting anywhere near catching up.) An on-call needs nothing more than 'sequence last_value is far below max(id)' to know the class of problem and that the fix is to fast-forward the sequence. What this free read does NOT give you is the rest of the estate -- which OTHER sequences in the database were left behind by the same restore -- and that fleet-wide view is the premium step.

This is a Pro lesson

Get every Learning Pathway and cookbook recipe -- grounded in PostgreSQL source code, with diagnostics, fixes, and prevention for each topic.

Continue this lesson to learn:

  • Advanced Approach -- Production-Safe
  • All 36 Learning Pathway lessons
  • 170+ cookbook recipes
  • Source-grounded diagnostics & fixes
Unlock everything -- $24.99/month $199/year -- save 35%

Secure checkout Cancel anytime Source-grounded

FIX

Fix

Advance the owning sequence past the highest id the restore loaded. One setval does it -- instant, no table lock of consequence, no data change -- and the very next INSERT succeeds.

SQL
SELECT setval(pg_get_serial_sequence('accounts','id'), (SELECT max(id) FROM accounts));
OUTPUT -- captured on real PostgreSQL
setval
--------
   1000
(1 row)
SQL
SELECT last_value, is_called FROM accounts_id_seq;
OUTPUT -- captured on real PostgreSQL
last_value | is_called
------------+-----------
       1000 | t
(1 row)
SQL
INSERT INTO accounts(email) VALUES ('[email protected]') RETURNING id;
OUTPUT -- captured on real PostgreSQL
id
------
 1001
(1 row)

INSERT 0 1

The fix is a single statement: SELECT setval(pg_get_serial_sequence('accounts','id'), (SELECT max(id) FROM accounts)). pg_get_serial_sequence resolves the owning sequence (accounts_id_seq) by name so nothing is hardcoded, and setval moves its counter to max(id) -- the call returns 1000. The capture then reads the sequence back to prove it now leads the data: last_value = 1000, is_called = t, meaning the next nextval will return 1001.

The INSERT that had been failing on every attempt now succeeds and returns id = 1001 -- the first id past all the loaded rows. Nothing else changed: no table was rewritten, no existing row was touched, and the id values from the restore are preserved exactly; only the sequence's internal counter moved.

That is the entire repair, and it is idempotent -- re-running setval to max(id) is harmless -- which is what makes the fleet-wide version safe to run across every desynced sequence after a restore.

VERIFY

Verify

Healthy-state proof: the sequence now leads the data, and ids keep climbing with no collisions -- the outage is over.

SQL
SELECT last_value, is_called FROM accounts_id_seq;
SELECT max(id) AS column_max_id FROM accounts;
OUTPUT -- captured on real PostgreSQL
last_value | is_called
------------+-----------
       1001 | t
(1 row)

 column_max_id
---------------
          1001
(1 row)
SQL
INSERT INTO accounts(email) VALUES ('[email protected]') RETURNING id;
INSERT INTO accounts(email) VALUES ('[email protected]') RETURNING id;
OUTPUT -- captured on real PostgreSQL
id
------
 1002
(1 row)

INSERT 0 1
  id
------
 1003
(1 row)

INSERT 0 1

The verify capture re-reads accounts_id_seq and confirms the new healthy state: last_value = 1001 (it advanced when the fix's INSERT consumed 1001), is_called = t, sitting at or just ahead of max(id) = 1001. It then runs two more ordinary INSERTs, which return id = 1002 and id = 1003, demonstrating that the sequence continues monotonically past the loaded data with no duplicate-key errors and no manual intervention per insert.

Taken with the fix capture, this is the before/after that pins the diagnosis beyond doubt: the table, its rows, and its constraints are unchanged -- only the sequence's counter moved -- so a lagging sequence was unambiguously the cause, and advancing it is unambiguously the cure. New rows now flow normally, each taking the next value above every id the restore loaded.

⏪ Rollback

Nothing here is destructive to the test environment, and the reproduction runs entirely against a throwaway database created and dropped on a PostgreSQL instance; the lab is torn down at the end and only the literal capture files are kept. No global server setting is touched. The one operational statement to understand before running it on a real system is the fix: setval(pg_get_serial_sequence('accounts','id'), (SELECT max(id) FROM accounts)). It is metadata-only and instantaneous -- it changes nothing but the sequence's internal counter, takes a brief lock on just the sequence, touches no table rows, and rewrites no data. It is also safe to re-run: setting the sequence to max(id) again is idempotent. The only judgement call is concurrency -- if rows are actively being inserted with explicit ids while you resync, compute max(id) and setval in a way that accounts for in-flight writes (e.g. briefly pause the explicit-id loader, or take max(id) inside the same statement as setval, as the recipe does). Setting the sequence too LOW is the thing to avoid: a value below max(id) reintroduces the very collisions you are fixing, and a value WAY above max(id) merely wastes id space (harmless for bigint, a real concern for an int4 column that is also approaching exhaustion). The prevention DO block only ever calls setval to the existing max(id) of each table, so it can never lower a sequence below its data; it skips any sequence already at or ahead of its max. None of this alters row content, constraints, or the table structure -- the id values already in the table are preserved exactly -- so there is nothing to 'undo': advancing the sequence is the cure, not a change to reverse.

📦 Version Matrix (PG 14--18)

The desync-then-collide behaviour is identical core behaviour on every supported major version. Each row is an independent run against a fresh throwaway database on that version's test environment node: load a serial table with explicit ids 1..100 without advancing the sequence, attempt a default INSERT, then setval to max(id) and insert again:

Version Behavior Version Notes
PG 14.23 default INSERT after explicit load: duplicate key value violates unique constraint ✗ Not available -- use stats_reset age
PG 15.18 default INSERT after explicit load: duplicate key value violates unique constraint ✗ Not available -- use stats_reset age
PG 16.14 default INSERT after explicit load: duplicate key value violates unique constraint ✗ Not available -- use stats_reset age
PG 17.10 default INSERT after explicit load: duplicate key value violates unique constraint ✗ Not available -- use stats_reset age
PG 18.4 default INSERT after explicit load: duplicate key value violates unique constraint ✗ Not available -- use stats_reset age

⚠ Common Mistakes

✗Inserting an EXPLICIT id into a serial / identity column does NOT advance the owning sequence -- this is the root cause. A data-only restore, a COPY of just the rows, or an INSERT ... SELECT that carries the id column all load data without moving the sequence, leaving it parked behind max(id). A full pg_dump/pg_restore is usually fine because pg_dump emits a setval() at the end of each table; the danger is loads that skip that setval.
✗The duplicate id in the error is being GENERATED by the sequence, not sent by your application. A 23505 whose DETAIL names a low id (Key (id)=(1)) on a table that already holds high ids is the signature of a lagging sequence, not of duplicate input data. Check the owning sequence's current value against max(id) before you go hunting for a bug in the client.
✗A failed INSERT still consumes a sequence value -- nextval() is non-transactional. That is why a desynced sequence creeps forward (last_value 1 -> 2 -> 3) as each retry fails, yet never catches up to a max(id) of thousands. Retrying the insert will not fix it; it just burns ids one at a time while still colliding.
✗Resync to max(id), not to an arbitrary number. setval(seq, max(id)) is the correct, idempotent repair. Setting the sequence BELOW max(id) reintroduces collisions; setting it absurdly high wastes id space (a real problem if the column is an int4 also approaching its 2147483647 ceiling). pg_get_serial_sequence(table, column) resolves the right sequence without hardcoding its name.
✗After a restore, fix ALL sequences, not just the table that happened to fail first. Every serial / identity column loaded with explicit ids is desynced; you simply have not tried to INSERT into the others yet. The fleet-wide resync (loop over pg_depend, setval each sequence whose last value is below its column's max) repairs them in one pass before they surface as separate 3am pages.
✗GENERATED ALWAYS AS IDENTITY does not let you sneak explicit ids in -- and that is a feature. A raw INSERT with an explicit id raises SQLSTATE 428C9 'cannot insert a non-DEFAULT value into column'. A loader must either let the DEFAULT assign ids (keeping the sequence in sync) or write OVERRIDING SYSTEM VALUE deliberately. Use it for new surrogate keys so a careless data-only load fails loudly instead of silently desyncing the sequence.
✗Concurrency matters when you resync a live table. If explicit-id inserts are still arriving while you run setval, max(id) can move under you. Briefly quiesce the loader, or compute max(id) and setval in a single statement, so you do not set the sequence to a value that an in-flight row has already claimed.
🔒 Pro · Prevent

Stop this incident before it pages you

The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.

  • ✓ A ready-to-paste alert query with the right thresholds.
  • ✓ The topology change that prevents recurrence.
  • ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Unlock Pro$24.99/mo · $199/yr

Frequently Asked Questions

Why does INSERT fail with 'duplicate key ... Key (id)=(1) already exists' when my table already has thousands of rows?

The table was loaded with explicit id values (typically a data-only restore or a bulk COPY/INSERT that carried its own id column), and inserting an explicit value into a serial/identity column does NOT advance the owning sequence. So the data marched up to id 1000 while the sequence stayed at 1. Your ordinary INSERT lets the DEFAULT supply the id, which calls nextval(), gets 1, and collides with the row that's already there -- SQLSTATE 23505. The duplicate isn't coming from your application; it's being generated by a sequence that fell behind its own data.

How do I fix it?

Advance the sequence past the loaded rows with a single setval(): SELECT setval(pg_get_serial_sequence('accounts','id'), (SELECT max(id) FROM accounts)). pg_get_serial_sequence resolves the owning sequence by name so you don't hardcode it; setval moves the counter to max(id), so the next nextval() returns max(id)+1. It's instantaneous, takes no table lock of consequence, rewrites no data, and is safe to re-run. After it, the INSERT that kept failing succeeds at 1001.

Why didn't a plain restore set the sequence correctly?

A full pg_dump/pg_restore usually does -- pg_dump emits a setval() at the end of each table's data so the sequence ends up at max(id). The desync comes from loads that skip that setval: pg_restore --data-only (where the setval may be filtered out), a COPY of just the rows, an INSERT ... SELECT that includes the id column, or a hand-written seed script. Any load that supplies explicit ids without a following setval leaves the sequence behind.

I retried the insert and it still fails, now complaining about id=2. Why?

Because nextval() is non-transactional: even a FAILED insert consumes a sequence value. So each retry nudges the sequence forward by one (1 -> 2 -> 3) and immediately collides with the next existing row. It will never catch up by retrying -- the table holds ids up to max(id), and you'd have to fail thousands of times to crawl past it. Run setval(seq, max(id)) once instead; it jumps the sequence past all the loaded data in a single step.

After a restore, how do I find and fix EVERY desynced sequence at once?

Run the fleet-wide resync: loop over pg_depend to map each sequence to its owning table.column, compute max(column) for each, and setval any sequence whose last value is below its column's max. The lab's prevention block does exactly this and prints which sequences it repaired (e.g. 'resynced public.imports_id_seq -> 500'), then a re-scan shows every sequence reporting seq_behind_data = f. Fixing them all in one pass beats discovering each one from a separate production error.

How do I prevent this from happening again?

Two things. Operationally, run the fleet-wide resync after ANY explicit-id load (restore, migration, import). Structurally, define surrogate keys as bigint GENERATED ALWAYS AS IDENTITY: such a column rejects a raw explicit-id INSERT with SQLSTATE 428C9 ('cannot insert a non-DEFAULT value into column'), so a careless data-only load fails loudly instead of silently leaving the sequence behind. A loader then must either let the DEFAULT assign ids (which keeps the sequence in sync) or write OVERRIDING SYSTEM VALUE on purpose -- a deliberate, visible choice rather than an accidental desync.

References

Keep going

Related & next steps

Browse the cookbook Step-by-step recipes Follow a pathway Learn it in order Open the glossary Every concept, plainly