PostgreSQL Tip: Resync table sequence

31 March 2026 · PostgreSQL · 1 min read

A quick one - for future reference.

The issue: occasionally after importing data into a database (a pg_restore, a bulk INSERT, a migration from another system), your primary key sequence can fall out of sync. New inserts then fail with duplicate key errors, because Postgres tries to hand out IDs that already exist.

To fix it, run this SQL snippet:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

It grabs the highest ID currently in the table and writes it back as the sequence’s position, so the next insert carries on from there.

If a whole import has left several tables in this state, you can do the lot from a Rails console in one go:

ActiveRecord::Base.connection.tables.each do |t|
  ActiveRecord::Base.connection.reset_pk_sequence!(t)
end

reset_pk_sequence! is a little-known ActiveRecord helper that does exactly the SQL above. Handy.

← All notes