PostgreSQL如何调整表主键ID序列使其从1开始连续排序

你之前执行的VACUUM、REINDEX仅用于清理表存储碎片、优化索引结构，不会修改现有主键值，也不会调整自增序列的计数，因此无法解决ID不连续问题。该问题的核心原因是truncate表时未同步重置自增序列，加上中途删除过数据条目，导致序列计数走高、现有ID存在断号。

单表操作步骤（你当前无关联表，可直接执行） #

以你提到的火车站表为例，替换语句中的表名即可通用到所有表：

• 第一步：重置现有数据ID为连续值
  执行如下SQL，将现有数据的ID从1开始重新排序：

  UPDATE train_station a
  SET id = b.new_id
  FROM (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS new_id
    FROM train_station
  ) b
  WHERE a.id = b.id;
  

  如需按其他规则排序ID，将ORDER BY id替换为对应字段即可，比如按站点名称排序可改为ORDER BY station_name。

• 第二步：重置自增序列起始值
  先查询主键列对应的自增序列名称：

  SELECT pg_get_serial_sequence('train_station', 'id');
  

  拿到序列名后执行如下语句，将后续新插入数据的ID起始值设置为当前最大ID+1：

  SELECT setval('你查询到的序列名', (SELECT MAX(id) FROM train_station));
  

  如果你使用的是PostgreSQL 10+的IDENTITY类型主键，可直接执行以下语句：

  ALTER TABLE train_station ALTER COLUMN id RESTART WITH (SELECT MAX(id)+1 FROM train_station);
  

多表批量处理方案 #

如果需要重置的表数量较多，可执行如下SQL生成所有表的批量操作语句，复制结果批量执行即可：

SELECT 
    'UPDATE ' || table_name || ' a SET id = b.new_id FROM (SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS new_id FROM ' || table_name || ') b WHERE a.id = b.id; ' ||
    'SELECT setval(pg_get_serial_sequence(''' || table_name || ''', ''id''), (SELECT MAX(id) FROM ' || table_name || '));' AS exec_sql
FROM information_schema.tables 
WHERE table_schema = 'public' -- 替换为你的表所属schema
AND table_type = 'BASE TABLE';

注意事项 #

• 操作前建议提前备份全表数据，避免误操作导致数据丢失
• 数据量较大时ID更新操作会锁表，建议在业务低峰期执行
• 操作完成后可执行SELECT COUNT(*) FROM 表名和SELECT MAX(id) FROM 表名验证两者数值一致，也可插入一条测试数据验证新ID是否为最大ID+1

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