ยท 1 min read
Reset a Postgres sequence
One ALTER SEQUENCE fixes the "duplicate key" failures that follow a manual data import.
A sequence that is behind the data is the classic symptom of a restored dump or a table populated with explicit IDs: the next INSERT tries an ID that already exists and Postgres raises a duplicate key error.
Reset it
ALTER SEQUENCE "sequence_name" RESTART WITH 1;
The next nextval() call returns 1. If your rows start at 1, that is wrong โ use setval instead:
SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders));
With the default is_called = true, the next value will be MAX(id) + 1.
Finding the sequence name
For a column with a serial or identity definition, the default name is <table>_<column>_seq:
SELECT pg_get_serial_sequence('orders', 'id');
Two cautions
Resetting below the current maximum will hand out IDs that already exist. Compute the starting point from the table, not from memory.
On a live table the reset is only safe while nothing else inserts. ALTER SEQUENCE itself is fast, but the gap between reading MAX(id) and writing it back is a race; run it in a transaction that locks the table, or accept a retry.