The symptom and the cause
After loading data into a table, the next ordinary insert fails with a duplicate key error on the primary key, even though the application never supplied an id. The cause is almost always that rows were loaded with explicit ids, bypassing the sequence that generates new ones. The sequence still thinks the next id is 1, or whatever it was before the load, and the first time it hands out a value already present, the insert collides.
It follows restores that load data only, imports from another database, copying rows between environments and tools that insert ids explicitly. The error can appear hours later, when the sequence finally reaches an occupied id, which makes it look unrelated to the import.
- Compare the table's highest id with the sequence's last value.
- Check whether the sequence belongs to an identity column, a serial column or a separate object.
Read the sequence against the data
Two numbers settle it: the maximum id in the table and the sequence's current state. If the sequence is at or below the maximum, the next value will collide. PostgreSQL's setval function sets the sequence; with the usual form, the next call to nextval returns the value after the one you set, so setting it to the table's maximum id makes the next insert use the following id. A three-argument form with false makes the next call return exactly the value given.
Do this on a restored copy first, and be careful about concurrency: setval takes effect immediately for every session, and the manual notes it is not undone if your transaction rolls back. Pause writers, or set the value with headroom if inserts continue while you work.
- Take the maximum id from the table at the moment you set the sequence.
- Check every table that was loaded, not only the one that reported the error.
Gaps are normal and are not the bug
Once fixed, you may notice missing numbers, and wonder whether rows were lost. PostgreSQL states that nextval values are not reclaimed if a transaction aborts, and that gaps can also arise without an abort, for instance when an INSERT ... ON CONFLICT computes the row, including its nextval, before it discovers a conflict. So sequences cannot be used to produce gapless numbering. If your business needs gapless invoice numbers, that is a separate design problem, not a sequence setting.
The reverse worry is also real: a restore that rewound the sequence can reuse ids that were once handed out and may still exist in logs, exports or other systems. Check for external references to recent ids before reusing them.
- Do not renumber rows to remove gaps.
- Check external systems that may hold old ids.
Prevent it, and where this fits
Tools that load data can reset sequences for you: pgloader's reset sequences option sets each sequence to the current maximum of its column after loading. If you load with COPY, which only appends rows and has no upsert, make resetting the sequence an explicit final step of the runbook, and put a check for it in the acceptance of any import or migration.
No fixed-price job in this catalogue covers this error: the duplicate-merge and add-column jobs do not include a sequence check, and this fault is not what they fix. The reset is a short step your engineer can take from the steps above. If the loaded rows came from a MySQL database, the separate guide on moving MySQL to PostgreSQL covers how auto-increment keys become sequences; a database move is its own job with its own checks.
- Add "insert a test row" to every import acceptance list.
- Record the sequence names and states in the handover.
Sources and limits
- PostgreSQL 18: Sequence manipulation functions Checked 2026-10-11.
- setval sets the sequence value, and with is_called true the next nextval returns the following value; nextval and setval are not rolled back, and sequences can have gaps so cannot be used for gapless numbering.
- PostgreSQL 18: COPY Checked 2026-10-11.
- COPY FROM appends rows to an existing table; it fails as a whole on an error by default and has no upsert option.
- pgloader: SQLite source Checked 2026-10-11.
- pgloader's reset sequences option sets sequences to the current maximum of their columns after loading.