PostgreSQL因序列失序导致BIGSERIAL主键插入重复报错

PostgreSQL的BIGSERIAL类型本质是语法糖，实现逻辑是：

1. 自动创建一个独立的序列对象
2. 给id字段设置默认值为nextval('关联序列名')

序列的计数器是独立维护的，不会自动感知表内手动插入的id值，也不会自动同步到表现有最大id。

你遇到的冲突是典型的序列计数器不同步问题：之前插入数据时，存在手动指定id值的写入操作，且这些手动写入的id值已经超过了序列当前的计数器位置。
对应你场景的实际逻辑：

• 序列当前计数器记录的最后生成值是7，下一次调用nextval会返回8
• 但表中已经存在id=8、id=9甚至更大值的手动插入行，这类手动指定id的插入操作不会更新序列计数器
• 此时不指定id插入数据，序列自动生成id=8，和已有行主键冲突触发报错；每执行一次插入，nextval就会让序列计数器+1，所以重试时报错里的id值会持续自增，直到计数器涨到表中不存在的id值为止。

执行以下SQL可以直接确认问题：

-- 查看person表id字段关联的序列名称
SELECT pg_get_serial_sequence('person', 'id');

-- 将上一步查询到的序列名替换到下方命令，查看序列当前计数器值
SELECT last_value FROM public.person_id_seq;

-- 查看person表中实际存在的最大id值
SELECT MAX(id) FROM person;

如果查询到的序列last_value小于表的MAX(id)，就会触发你遇到的主键冲突。

执行以下语句将序列计数器重置为表内最大id值，后续自动生成的id就会从最大id+1开始，不会再出现冲突：

SELECT setval(
  pg_get_serial_sequence('person', 'id'),
  (SELECT MAX(id) FROM person)
);

• BIGSERIAL只保证序列自身生成的值不重复，不会校验手动插入的id是否和后续序列生成值冲突
• 后续如果做批量数据导入、手动指定id插入数据的操作，插入完成后都需要执行一次上述setval语句同步序列计数器，避免重复触发同类问题。

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