PostgreSQL

PostgreSQL Sequence Out of Sync After Manual Insert

When a PostgreSQL sequence falls out of sync — typically after manually inserting a row with an explicit ID — the database's auto-increment counter doesn't know about that manually specified value, and eventually collides with it once the sequence catches up on its own.

The Problem

After manually inserting a row with a specific ID (common during data migration, testing, or fixing bad data), a later normal insert relying on auto-increment fails:

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

The application didn't specify an ID at all for this insert — it relied on the database to generate one automatically — yet the generated value collides with something that already exists.

Why It Happens

PostgreSQL sequences track "the next value to hand out" independently of what actually exists in the table. When you manually insert a row with an explicit ID, the sequence has no way of knowing that value was just used, since sequence advancement only happens automatically when a value is generated by the sequence itself, not when a value is inserted directly. Common causes:

  • Manually inserting rows with explicit ID values during data seeding, testing, or fixing a data issue, without also advancing the sequence to account for those IDs
  • Restoring a database backup or migrating data from another source where rows carry their original IDs, but the sequence wasn't reset afterward to reflect the highest existing ID
  • Multiple environments (like a copy of production data loaded into staging) where the sequence value from the source database wasn't preserved or correctly recalculated after the data was loaded

The Fix

First, check the sequence's current value versus the actual maximum ID currently in the table:

SELECT last_value FROM orders_id_seq;
SELECT MAX(id) FROM orders;

If the sequence's last_value is lower than the actual maximum ID in the table, that gap is exactly what causes the eventual collision — the sequence will keep handing out values starting from where it thinks it left off, walking straight into IDs that already exist.

Reset the sequence to correctly reflect the current maximum ID in the table:

SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders));

This sets the sequence so the next generated value will be one greater than the current maximum, avoiding any collision with existing data. If the table might be empty (which would make MAX(id) return null and cause an error), use a safer version with a fallback:

SELECT setval('orders_id_seq', COALESCE((SELECT MAX(id) FROM orders), 1));

To find the actual sequence name if you're not sure of it (sequence names are often auto-generated based on the table and column, but can vary):

SELECT pg_get_serial_sequence('orders', 'id');

Going forward, if you need to manually insert rows with explicit IDs regularly (for example, during data seeding scripts), make it a habit to reset the sequence immediately afterward as part of the same script, rather than treating it as a separate manual cleanup step that's easy to forget.

Still Not Working?

If you're restoring from a backup regularly (like refreshing a staging environment from a production dump) and this keeps recurring, consider adding the sequence reset as a standard final step in your restore process, applied to every sequence in the database at once rather than one at a time. A short script that iterates over all sequences and resets each based on its corresponding table's actual maximum ID can be run automatically after every restore, eliminating this as a recurring manual task:

SELECT setval(pg_get_serial_sequence('"' || tablename || '"', 'id'),
              COALESCE((SELECT MAX(id) FROM orders), 1))
FROM pg_tables WHERE schemaname = 'public';

It's also worth understanding the difference between setval's two-argument and three-argument forms, since the third argument changes behavior in a way that matters here. The two-argument version used above sets the sequence so the next call to nextval() returns one more than the value provided. If you instead want the exact value provided to be the very next one returned (rather than one greater), use the three-argument form with false for the "is_called" parameter:

SELECT setval('orders_id_seq', 105, false);
-- next nextval() call returns exactly 105, not 106

Getting this distinction backwards is a subtle, easy-to-make mistake that can leave the sequence off by exactly one, producing either an unnecessary gap in IDs or, in the worst case, one more collision before things stabilize.

Finally, if this keeps happening in a production environment specifically (rather than just during development or testing), it's worth reconsidering whether manual ID insertion should be happening at all in that environment. In most well-designed systems, production data should flow exclusively through the application's normal insert paths, which always use the sequence correctly by default — manual, explicit-ID inserts are typically a sign of a one-off fix or migration that deserves the sequence-reset step built directly into that same script, rather than left as a separate manual cleanup task someone has to remember later.

You might also like