如何批量重置PostgreSQL中所有不同步的表主键序列？

Nice question—this is such a common pain point after importing data with tools like Postico, where manual inserts throw off auto-increment sequences. Instead of running that setval command one by one, here are two solid ways to batch-fix all your tables:

1. Generate All Sync Queries First (Safe & Transparent) #

If you want to review exactly what commands will run before executing them, use this query to generate the setval statements for every table with an id auto-increment column:

SELECT format(
  'SELECT pg_catalog.setval(pg_get_serial_sequence(''%I.%I'', ''%I''), (SELECT COALESCE(MAX(%I), 0) + 1 FROM %I.%I));',
  n.nspname,
  c.relname,
  a.attname,
  a.attname,
  n.nspname,
  c.relname
) AS sync_command
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_attribute a ON c.oid = a.attrelid
JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
WHERE c.relkind = 'r' -- Target only regular tables
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND ad.adsrc LIKE 'nextval(%' -- Filter columns using auto-increment sequences
  AND a.attname = 'id'; -- Adjust this if your auto-increment column has a different name

Run this query, copy all the outputted sync_command lines, and execute them in one go. The COALESCE ensures even empty tables get their sequences set to start at 1 instead of breaking on NULL.

2. Automate with a PL/pgSQL Function (One-Click Fix) #

If you prefer to automate the whole process without copying/pasting, create a function that loops through all qualifying tables and runs the sync automatically:

CREATE OR REPLACE FUNCTION sync_all_id_sequences()
RETURNS void AS $$
DECLARE
  sync_rec record;
BEGIN
  FOR sync_rec IN
    SELECT
      n.nspname AS schema_name,
      c.relname AS table_name,
      a.attname AS column_name
    FROM pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    JOIN pg_attribute a ON c.oid = a.attrelid
    JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
    WHERE c.relkind = 'r'
      AND a.attnum > 0
      AND NOT a.attisdropped
      AND ad.adsrc LIKE 'nextval(%'
      AND a.attname = 'id' -- Match your auto-increment column name
  LOOP
    EXECUTE format(
      'SELECT pg_catalog.setval(pg_get_serial_sequence(''%I.%I'', ''%I''), (SELECT COALESCE(MAX(%I), 0) + 1 FROM %I.%I));',
      sync_rec.schema_name,
      sync_rec.table_name,
      sync_rec.column_name,
      sync_rec.column_name,
      sync_rec.schema_name,
      sync_rec.table_name
    );
    RAISE NOTICE 'Successfully synced sequence for: %.%.%', sync_rec.schema_name, sync_rec.table_name, sync_rec.column_name;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Run the function to sync all sequences
SELECT sync_all_id_sequences();

This will loop through every table with an id auto-increment column, update its sequence, and print a notice for each table it processes. If you don't need the notices, just remove the RAISE NOTICE line.

Quick Notes: #

• If your auto-increment columns aren't named id, just modify the a.attname = 'id' condition to match your column name (e.g., user_id, post_id).
• This works for both old-style serial columns and newer PostgreSQL 10+ IDENTITY columns, since pg_get_serial_sequence handles both.

内容的提问来源于stack exchange，提问作者Navid Khan