The insert that fails right after a successful load in PostgreSQL

There's a class of failure in PostgreSQL that fools even experienced people: the load finishes without complaint, the row counts match the fixtures file, every foreign key resolves, CI goes green, and then the application tries to insert a single row of its own and the database refuses with duplicate key value violates unique constraint. Nothing is corrupted, nothing needs to be restored. What exists is a sequence out of sync with the table it feeds, and the article by Mikhail Shytsko, founder of Seedfast, published on Planet PostgreSQL, dissects the mechanism with examples run against PostgreSQL 18.6.

The central point is conceptual before it's operational: explicit ids and generated ids come from different places, and loading the former doesn't move the latter. It's a classic case where the problem isn't in performance or in the execution plan, but in the design of the load flow.

Why the sequence goes out of sync

A bigserial column is, in practice, a bigint carrying a default of nextval(''). When the INSERT supplies its own id value, that default simply never gets evaluated, and the sequence stays put while the table fills up around it. The source's example is straightforward:

CREATE TABLE users (id bigserial PRIMARY KEY, email text NOT NULL UNIQUE);

INSERT INTO users (id, email)
VALUES (1, 'a@example.com'), (2, 'b@example.com'), (3, 'c@example.com');

SELECT last_value, is_called FROM users_id_seq;
-- last_value = 1, is_called = f

Three rows in the table and the sequence still reports its creation state: last_value equal to 1 with is_called set to false, which together mean that the value 1 hasn't been handed out yet. The next insert that lets Postgres choose the id asks for 1, and 1 already belongs to the row loaded seconds earlier. Hence the delayed error, which is exactly what confuses people: the seed ran, CI passed, and the failure sat waiting for the first write that a human or a test actually performs.

Identity columns don't fix this

Since PostgreSQL 10, the SQL-standard-compliant spelling is the identity column, and teams that migrated from serial sometimes assume the problem migrated along with it. It didn't. With GENERATED BY DEFAULT AS IDENTITY, writing explicit ids goes through without complaint, and the collision arrives just the same on the next insert.

The GENERATED ALWAYS variant is stricter, and in this case the strictness helps: it refuses the load immediately and prints the output below, HINT: Use OVERRIDING SYSTEM VALUE to override. The important detail is that following the hint fixes nothing. OVERRIDING SYSTEM VALUE only suspends the check that blocks writing your own value and has absolutely nothing to say about the sequence, which stays parked at 1 while the rows go in. The strictness turns a silent trap into an immediate complaint, but whoever works around the complaint inherits exactly the same stale sequence.

The one-line fix, and two ways to get it wrong

The fix points the sequence to the highest value the table contains:

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

The next insert returns 4 and the incident is over. But there are two mistakes that most recipes on the internet make.

Don't hardcode the sequence name. Almost every version of this snippet writes users_id_seq directly, which is correct until someone renames the table. Sequences don't follow the rename:

ALTER TABLE users RENAME TO members;
SELECT pg_get_serial_sequence('members', 'id');
-- public.users_id_seq

The table is now members, but the sequence is still called users_id_seq. A script that builds the name by string concatenation ends up targeting a sequence that has nothing to do with the table it thinks it's fixing. The pg_get_serial_sequence() function consults the catalog instead of guessing, and it also works for identity columns, despite the "serial" in its name.

Watch out for the empty table. Over zero rows, max(id) is NULL, and since setval is strict, passing NULL makes the call return without touching anything. That's harmless on a freshly created sequence, but silently wrong on one that a previous run has already advanced, a typical situation for suites that TRUNCATE between rounds. The form that survives both cases carries its own is_called argument:

SELECT setval(pg_get_serial_sequence('empty2', 'id'), coalesce(max(id), 1), max(id) IS NOT NULL)
FROM empty2;

The third argument is the root of a common mystery. If you've ever wondered why a reset left the numbering starting at 2 instead of 1, it's the is_called flag. The two-line experiment makes it clear:

SELECT setval('flagcheck_id_seq', 10);        -- next insert gets 11
SELECT setval('flagcheck_id_seq', 10, false);  -- next insert gets 10

Resetting identity on its own terms

Identity columns have native syntax that never touches the sequence's name:

ALTER TABLE t_ident ALTER COLUMN id RESTART WITH 3;

It's more readable and is checked at parse time, but it only covers identity: pointing it at a serial column throws an explicit error. The setval form works for both types, which is worth something for a script that needs to fix an entire schema without branching. And when the table is disposable rather than seeded, TRUNCATE t_ident RESTART IDENTITY empties it and rewinds the sequence to 1 in a single command, working equally for serial and identity. It's worth stressing: a plain TRUNCATE removes the rows and leaves the sequence exactly where it was.

Realigning an entire schema after the load

Fixing one table by hand works for a single incident. After a bulk load on a reasonably sized schema, what you want is to find and move every affected sequence without naming any of them, and the catalog has enough information for that. Shytsko proposes a DO block that walks information_schema.columns, resolves each column's sequence via pg_get_serial_sequence(), and runs the idempotent setval with the correct coalesce and flag. The example output, with NOTICE: realigned public.users_id_seq for members.id, catches the rename trap in action: the sequence behind members.id is still called users_id_seq. The recommendation is to run this once at the end of the load, instead of scattering setval calls across the fixtures file, where they rot every time a table is added.

Or stop writing explicit ids

The author is honest in calling all of this 'patching a self-inflicted wound.' The ids show up in the load because a fixtures file wants user 1 to be Alice so assertions can rely on it. That convenience is what puts the sequence and the table on separate tracks. There are two real ways out: let the database assign the ids and capture them with RETURNING or a CTE, so that no literal id appears in the file, or generate the data instead of writing it by hand. The second is Seedfast's route, the author's own product, which is worth keeping in mind while reading the recommendations. Neither one, however, helps with a dump restored this morning; for that scenario, the realignment DO block remains the right tool.

The practical value for anyone administering production databases is recognizing the pattern early: duplicate key right after a clean load is rarely corruption and almost never requires a restore. It's an out-of-sync sequence, and the fix is cheap as long as you resist hardcoding the sequence's name and handle the empty-table case.