Reset PostgreSQL sequences after importing data with explicit IDs

You finished a MySQL to PostgreSQL migration, the data looks right, and then the first insert your app makes blows up:

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

The rows are fine. The problem is the sequence behind the primary key. This page explains why it happens and gives you a one-liner per table plus a script that fixes every table at once.

Why it happens

A PostgreSQL SERIAL or IDENTITY column draws new values from a sequence, and the sequence is only advanced when Postgres itself supplies the value. When you load a dump that already contains explicit id values, the rows go in but the sequence counter never moves — it is still sitting at 1. The next insert that omits the id asks the sequence for a number, gets a low one that a migrated row already uses, and violates the primary key. The fix is to fast-forward the sequence past the largest ID currently in the table.

Fix one table

Use setval() together with pg_get_serial_sequence() so you never have to know the sequence's real name — it works for both SERIAL and IDENTITY columns:

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

After this, MAX(id) is stored as "already used", so the next value handed out is MAX(id) + 1. One caveat: on an empty table MAX(id) is NULL and setval() rejects it. This version is safe whether the table has rows or not:

SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  GREATEST(COALESCE((SELECT MAX(id) FROM users), 0), 1),
  (SELECT COUNT(*) > 0 FROM users)
);

The third argument is is_called: true means "the value is used, hand out the next one", false means "hand out this value first". An empty table therefore restarts cleanly at 1.

Fix an identity column the native way

If the column is a true identity column (GENERATED ... AS IDENTITY, Postgres 10+), you can restart it directly. Compute one past the current maximum and run:

ALTER TABLE users ALTER COLUMN id RESTART WITH 1043;

This RESTART WITH form only works on identity columns; for a SERIAL column stick with the setval() approach above. Not sure which you have? In psql run \d users: an identity column reads generated ... as identity, while a serial column shows default nextval('users_id_seq').

Fix every table at once

After a full-database import you want to reset all of them. This block walks every serial and identity column in the current schema and fixes each sequence, empty tables included:

DO $$
DECLARE
  r     RECORD;
  seq   TEXT;
  maxid BIGINT;
BEGIN
  FOR r IN
    SELECT table_schema, table_name, column_name
    FROM   information_schema.columns
    WHERE  table_schema NOT IN ('pg_catalog', 'information_schema')
      AND (column_default LIKE 'nextval(%' OR is_identity = 'YES')
  LOOP
    seq := pg_get_serial_sequence(
             quote_ident(r.table_schema) || '.' || quote_ident(r.table_name),
             r.column_name);
    CONTINUE WHEN seq IS NULL;
    EXECUTE format('SELECT COALESCE(MAX(%I), 0) FROM %I.%I',
                   r.column_name, r.table_schema, r.table_name)
      INTO maxid;
    EXECUTE format('SELECT setval(%L, %s, %s)',
                   seq, GREATEST(maxid, 1), maxid > 0);
  END LOOP;
END $$;

It skips columns whose sequence is not owned by the table (a manually created sequence used as a default), which pg_get_serial_sequence reports as NULL. Run it once and every auto-numbered table is back in sync.

Avoid the problem entirely

This whole class of error only exists because a raw dump import leaves the sequences untouched. SwapSQL advances every sequence past your imported data as part of the conversion, so the first insert after you switch over just works.

Migrating a database? Convert MySQL to PostgreSQL with sequences set for you →