Lorixen.
EN

Duplicate key errors after an import? Reset the Postgres sequence

Short answer: If inserts start failing with "duplicate key value violates unique constraint" on a table's primary key right after you imported data, restored a backup or copied rows in by hand, the table's ID sequence is behind. The rows you loaded used IDs the sequence never handed out, so it's now offering numbers that already exist. Find the sequence with pg_get_serial_sequence, then move it past the highest existing ID with setval. Inserts work again immediately, and nothing in your data needs to change.

Why this happens

PostgreSQL auto-increment columns, whether serial or identity columns, get their values from a sequence: a small counter object that hands out the next number each time. The documentation describes nextval as advancing the sequence and returning the new value atomically, so concurrent sessions never get the same number.

The sequence only knows about numbers it has handed out itself. If rows arrive with IDs supplied explicitly, the sequence isn't told:

  • A bulk load or migration script inserts rows with their original IDs from an old system.
  • A developer copies rows from production to staging, including IDs.
  • A restore or data merge loads tables but sequences end up at an older value.
  • Someone inserts a few records by hand with chosen IDs to "fill a gap".

Afterwards the table holds IDs up to, say, 50,000, but the sequence's next value might be 1,200. The first new insert asks for 1,200, finds it taken and fails. Each failed insert still consumes a number, so the error repeats for every request until the sequence catches up or is fixed.

Confirm the diagnosis

  1. Read the error. It names the constraint, typically the table's primary key, such as orders_pkey, and the key value it tried, such as (id)=(1200).
  2. Find the highest existing ID: SELECT max(id) FROM orders;
  3. Find the sequence: SELECT pg_get_serial_sequence('orders', 'id'); The documentation describes this function as returning the name of the sequence associated with a column, or NULL if there isn't one.
  4. Compare the sequence's current state with the maximum ID. If the sequence is lower, you've found the problem.

The fix

Set the sequence to the current maximum so the next value is one higher:

SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT max(id) FROM orders));

The PostgreSQL documentation explains that the two-argument form of setval sets the sequence's last value and marks it as called, so the next nextval returns the following number. For an empty table, where max(id) is NULL, set it explicitly to 1 with the third argument false, so the next value is 1.

Run this for every table you imported into, not just the one that failed first. Errors will appear one table at a time otherwise.

Fixing many tables at once

After a big import, it's worth checking all tables with auto-increment columns. A short query or script can loop over columns with an associated sequence, using pg_get_serial_sequence, and run the same setval for each table's maximum. Run it on staging first, then production, ideally during a quiet period. Some migration and ORM tools include a command that resets sequences for you.

Identity columns and explicit IDs

Newer schemas often use identity columns instead of serial. The PostgreSQL documentation describes two kinds:

  • GENERATED ALWAYS AS IDENTITY: an explicitly supplied value is only accepted if the insert specifies OVERRIDING SYSTEM VALUE. This makes accidental explicit IDs much harder.
  • GENERATED BY DEFAULT AS IDENTITY: a supplied value takes precedence, which behaves more like serial, so the same drift can happen.

Either way, the identity column is backed by a sequence, and the same setval fix applies after loading rows with explicit IDs.

Preventing it next time

  • Let the database assign IDs for new data wherever possible; only keep original IDs when you truly need them, for example to preserve references.
  • Add a "reset sequences" step to every import, migration and data-copy script, right after the load.
  • Use GENERATED ALWAYS for new tables if your team is prone to inserting explicit IDs.
  • Check after restores. Make "inserts work on every main table" part of your restore checklist.

When the parent table's sequence was behind, child tables usually are too: order items, invoice lines, payments, notes and audit entries often all received explicit IDs in the same import. Fix them in the same sitting. While you're there, confirm that foreign keys between imported tables still point to the right rows, by spot-checking a few orders end to end in the app. A sequence problem is quick to fix; a broken reference discovered weeks later is not.

Don't "fix" it by deleting rows

When the error appears, it's tempting to delete the conflicting row or change its ID. That damages real data and doesn't fix the cause; the next insert will hit the next existing ID. Move the sequence instead.

A worked example

Picture a hypothetical Portland coffee roaster that moved its wholesale ordering app to a new database by exporting tables from the old system and loading them with their original IDs. The first morning after the switch, every new wholesale order failed with a duplicate key error on orders_pkey. The developer compared max(id) with the sequence, found the sequence far behind, and ran setval for the orders, order items, customers and invoices tables. Orders went through immediately, and the reset step was added to the migration script for the next move.

FAQ

Will setval change existing data?

No. It only changes the counter used for future inserts.

Can sequences have gaps afterwards?

Yes, and that's normal. Sequences don't guarantee gap-free numbers. Don't use them where unbroken numbering matters.

Why did the error appear only on some inserts?

Each failed insert advances the sequence. In tables with gaps in existing IDs, some new values happen to be free and succeed.

Do I need to restart the app?

No. The next insert uses the new sequence value.

If your app started throwing duplicate key errors after a data move, tell us which tables are affected and we'll help reset them safely.

Sources

WhatsAppBook a call