Postgres 9.6：将所有表的序列当前值设为对应表最大ID值

PostgreSQL中使用SERIAL类型定义主键时，会自动为字段创建关联序列实现自增。但手动插入主键值后，序列的当前值不会同步更新到字段的最大值，后续自动生成的主键可能出现冲突。单表可手动执行setval语句修正，但多表场景下手动操作效率极低。

通用批量处理方案 #

通过查询PostgreSQL系统元数据表，可自动生成所有SERIAL关联序列的setval执行语句，一次性完成批量更新：

SELECT format(
  'SELECT setval(''%I'', COALESCE((SELECT MAX(%I) FROM %I), 1), false);',
  seq.relname,
  col.column_name,
  tbl.relname
) AS sql_statement
FROM pg_class AS seq
JOIN pg_depend AS dep ON seq.oid = dep.objid
JOIN pg_class AS tbl ON dep.refobjid = tbl.oid
JOIN pg_attribute AS attr ON dep.refobjid = attr.attrelid AND dep.refobjsubid = attr.attnum
JOIN information_schema.columns AS col 
  ON tbl.relname = col.table_name 
  AND attr.attname = col.column_name
WHERE seq.relkind = 'S'
  AND col.data_type IN ('smallint', 'integer', 'bigint')
  AND col.column_default LIKE 'nextval(%'')'
ORDER BY tbl.relname, col.column_name;

执行说明 #

1. 运行上述SQL，会生成一系列针对每个SERIAL字段的setval语句
2. 将生成的所有sql_statement结果复制出来，直接执行即可完成所有序列的同步
3. 若需自动执行（无需手动复制），可使用DO块封装（需确保权限足够）：

DO $$
DECLARE
  rec record;
BEGIN
  FOR rec IN (
    SELECT format(
      'SELECT setval(''%I'', COALESCE((SELECT MAX(%I) FROM %I), 1), false);',
      seq.relname,
      col.column_name,
      tbl.relname
    ) AS sql
    FROM pg_class AS seq
    JOIN pg_depend AS dep ON seq.oid = dep.objid
    JOIN pg_class AS tbl ON dep.refobjid = tbl.oid
    JOIN pg_attribute AS attr ON dep.refobjid = attr.attrelid AND dep.refobjsubid = attr.attnum
    JOIN information_schema.columns AS col 
      ON tbl.relname = col.table_name 
      AND attr.attname = col.column_name
    WHERE seq.relkind = 'S'
      AND col.data_type IN ('smallint', 'integer', 'bigint')
      AND col.column_default LIKE 'nextval(%'')'
  ) LOOP
    EXECUTE rec.sql;
  END LOOP;
END $$;

注意事项 #

• 执行用户需对所有涉及的表拥有SELECT权限，对序列拥有USAGE权限
• 若表中无数据，COALESCE会将序列值设为1，false参数表示下一次nextval直接返回该值（符合SERIAL类型默认自增逻辑）
• 该方案会自动匹配所有由SERIAL/BIGSERIAL/SMALLSERIAL创建的关联序列，无需手动指定表名或序列名

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