The Postgres Insert That Fails Right After a Successful Load

Identity columns do not save you

Since Postgres 10 the standard-conforming spelling is an identity column, and teams that moved off serial sometimes assume the problem moved with it, which it didn't.

CREATE TABLE t_ident (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, x text);

INSERT INTO t_ident (id, x) VALUES (1, 'a'), (2, 'b');
INSERT INTO t_ident (x) VALUES ('c');

ERROR:  duplicate key value violates unique constraint "t_ident_pkey"
DETAIL:  Key (id)=(1) already exists.

GENERATED ALWAYS is stricter and, for once, the strictness is useful, because it turns a silent trap into an immediate complaint with the workaround printed underneath:

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

Take the hint, and the load succeeds. So does the collision that follows it, because OVERRIDING SYSTEM VALUE only suspends the check that blocks your value from being written and has nothing at all to say about the sequence underneath, which stays parked at 1 while rows 1 and 2 go in.

The one-line fix, and two ways it is usually written wrong

Point the sequence at the largest value the table currently holds:

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

 setval
--------
      3
(1 row)

The next insert now returns id 4 and the incident is over, though two details in that line are worth more than the line itself.

Do not hardcode the sequence name! Nearly every version of this snippet on the internet writes users_id_seq directly, which is correct until somebody renames the table. Sequences don't follow:

ALTER TABLE users RENAME TO members;
SELECT pg_get_serial_sequence('members', 'id');

 pg_get_serial_sequence
------------------------
 public.users_id_seq
(1 row)

The table is members, the sequence is still users_id_seq, and a seed script that builds the name by string concatenation now targets a sequence that has nothing to do with the table it thinks it is fixing. pg_get_serial_sequence() asks the catalog instead of guessing, and it answers for identity columns too, despite the "serial" in its name.

Watch the empty table. Over zero rows max(id) is NULL, and because setval is strict, handing it a NULL means the call comes back without touching anything:

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

 setval
--------

(1 row)

Postgres neither errors nor changes anything, which is harmless on a freshly created sequence and quietly wrong on one that an earlier run already advanced, the situation you're in whenever a suite truncates tables between runs. 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;

Where rows exist, max(id) becomes the last value used and the flag is true. An empty table takes the other branch, landing the sequence on 1 with the flag false, so the very next nextval hands out 1 rather than 2. If you have ever wondered why a reset left you starting at 2, that flag is the reason, and the difference shows up in a two-line experiment:

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

Resetting an identity column on its own terms

Identity columns have native syntax that never touches a sequence name:

ALTER TABLE t_ident ALTER COLUMN id RESTART WITH 3;

The next insert returns 3. Compared with setval it reads better and gets checked at parse time, though it covers identity columns only, so pointing it at a serial column tells you so plainly:

ERROR:  column "id" of relation "users" is not an identity column

Which of the two you reach for is mostly a question of how the table is declared, though the setval form works on both kinds, which is worth something to a script that has to fix a whole schema without branching. When the table is disposable rather than seeded, TRUNCATE t_ident RESTART IDENTITY empties it and rewinds the sequence to 1 in a single statement, for serial and identity alike.

Realigning a whole schema after a load

Fixing one table by hand is fine for an incident. After a bulk load into a schema of any size you want every affected sequence found and moved without naming any of them, and the catalog knows enough to do that on its own:

DO $$
DECLARE r record;
BEGIN
  FOR r IN
    SELECT c.table_schema AS s, c.table_name AS t, c.column_name AS col,
           pg_get_serial_sequence(format('%I.%I', c.table_schema, c.table_name), c.column_name) AS seq
    FROM information_schema.columns c
    JOIN information_schema.tables tb
      ON tb.table_schema = c.table_schema AND tb.table_name = c.table_name
    WHERE c.table_schema = 'public' AND tb.table_type = 'BASE TABLE'
  LOOP
    IF r.seq IS NOT NULL THEN
      EXECUTE format('SELECT setval(%L, coalesce(max(%I), 1), max(%I) IS NOT NULL) FROM %I.%I',
                     r.seq, r.col, r.col, r.s, r.t);
      RAISE NOTICE 'realigned % for %.%', r.seq, r.t, r.col;
    END IF;
  END LOOP;
END $$;

NOTICE:  realigned public.t_ident_id_seq for t_ident.id
NOTICE:  realigned public.users_id_seq for members.id
NOTICE:  realigned public.t_always_id_seq for t_always.id
NOTICE:  realigned public.empty_t_id_seq for empty_t.id
NOTICE:  realigned public.empty2_id_seq for empty2.id

Serial columns and identity columns come out the same way, because pg_get_serial_sequence() treats them the same, and the second line of that output catches the rename trap in the act, since the sequence behind members.id is still called users_id_seq. Run this once at the end of a load rather than sprinkling setval calls through a fixture file, where they rot every time a table is added.

Or stop writing explicit ids

Every fix above is repair work on a self-inflicted wound. The ids exist in the load because a fixture file wanted user 1 to be Alice for assertions to hang off, and that convenience is what puts the sequence and the table on separate tracks in the first place, so it's a fair trade right up to the moment somebody inserts a row.

However, there are two ways out. Let the database assign ids and capture them with RETURNING or a CTE, so no literal id ever appears in the file, or just generate the data instead of writing it down. That is the route Seedfast takes, and rows that come out of a generator carry no hardcoded keys, so there's nothing to realign afterwards.

Neither helps with the dump you restored this morning - for that, the DO block above is what you want.

Frequently asked questions

Related guides

• Writing a Postgres seed script that survives the next migration. Where the sequence reset belongs inside a maintained seed.sql, alongside idempotency and insert ordering.
• PostgreSQL test data: a syntax cookbook. The generate_series, bulk copy and pg_dump mechanics behind the load that gets you into this state.
• Circular foreign key seed: three workarounds that actually run. The other constraint problem that only shows up at insert time.
• Get started with Seedfast. Generate connected rows against your own schema instead of maintaining a fixture file.