Auto-Syncing PostgreSQL Sequences: The One-Query Fix You’ll Wish You Knew Sooner

aiAssisted coding database postgres sql
TL;DR

If you’ve ever restored data, merged dumps, or manually inserted IDs in PostgreSQL, you may have noticed that your SERIAL or IDENTITY columns start throwing duplicate key errors. That’s because the sequence that generates new IDs no longer matches the table’s current maximum. This one-query DO block scans your entire schema, finds every sequence-backed column, and automatically resets each sequence to MAX(id)+1 — safely, cleanly, and in a single pass.

💡 The Problem

When you use SERIAL or GENERATED BY DEFAULT AS IDENTITY, PostgreSQL creates a hidden sequence that keeps track of your next auto-increment value.

But if you:

  • import data manually,
  • copy tables from backups, or
  • run bulk inserts that specify explicit IDs,

…the table’s maximum ID can get out of sync with the sequence. Next insert? Boom — ERROR: duplicate key violates unique constraint.

🧰 The Fix: One DO Block to Rule Them All

This script dynamically loops through every sequence in a schema (e.g. my_schema) and sets each one’s value to MAX(id)+1 for its corresponding table. 

DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT
      tbl_ns.nspname AS schema_name,
      tbl.relname AS table_name,
      col.attname AS column_name,
      seq_ns.nspname AS seq_schema,
      seq.relname AS seq_name
    FROM pg_class seq
    JOIN pg_depend dep ON dep.objid = seq.oid AND dep.deptype = 'a'
    JOIN pg_class tbl ON dep.refobjid = tbl.oid AND tbl.relkind IN ('r','p')
    JOIN pg_namespace tbl_ns ON tbl_ns.oid = tbl.relnamespace
    JOIN pg_attribute col ON col.attrelid = tbl.oid AND col.attnum = dep.refobjsubid
    JOIN pg_namespace seq_ns ON seq_ns.oid = seq.relnamespace
    WHERE seq.relkind = 'S'
      AND tbl_ns.nspname = 'my_schema'
  LOOP
    EXECUTE format(
      'SELECT setval(%L,
                     COALESCE((SELECT MAX(%I) FROM %I.%I), 1),
                     (SELECT MAX(%I) IS NOT NULL FROM %I.%I));',
      format('%I.%I', r.seq_schema, r.seq_name),
      r.column_name, r.schema_name, r.table_name,
      r.column_name, r.schema_name, r.table_name
    );
  END LOOP;
END
$$;

✅ What it does

  • Finds every table/column/sequence pair in your schema.
  • Checks the current max ID (MAX(column_name)).
  • Updates the sequence with setval(seq, max_id, true) or resets to 1 if empty.
  • No hard-coding, no assumptions, fully schema-aware.

⚙️ When to Use It

  • After restoring or importing data from a dump
  • After bulk inserts that manually specify IDs
  • Before running migrations that rely on auto-increment
  • Anytime you get “duplicate key” errors on serial/identity columns

🧠 Pro Tip

You can preview what it will do before running it:

🚀 In Summary

SELECT format(
  'SELECT setval(%L, COALESCE((SELECT MAX(%I) FROM %I.%I), 1), (SELECT MAX(%I) IS NOT NULL FROM %I.%I));',
  seq_qual, column_name, schema_name, table_name, column_name, schema_name, table_name
)
FROM your_schema_metadata_view;

With a single query, you can bring all your PostgreSQL sequences back in sync — no manual fixes, no guesswork. Run it once, sleep better forever.

more articles