bytama

ยท 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.