如何修复PostgreSQL表与序列绑定异常并调整绑定序列？

问题原因 #

当你通过Navicat将serial类型列转为GENERATED BY DEFAULT AS IDENTITY时，工具没有复用原serial对应的sys_role_id_seq序列，而是新建了带后缀1的sys_role_id_seq1序列并绑定到id列，但pg_get_serial_sequence仍会识别原serial创建的序列，导致查询结果与实际绑定序列不符。

修复步骤 #

1. 确认当前列绑定的序列 #

先验证当前id列实际使用的序列：

SELECT column_default FROM information_schema.columns 
WHERE table_schema = 'public' AND table_name = 'sys_role' AND column_name = 'id';

返回结果应显示nextval('sys_role_id_seq1'::regclass)，确认当前绑定的是带后缀的序列。

2. 同步旧序列的当前值 #

确保旧序列sys_role_id_seq的当前值不低于表中已有的最大id，避免后续插入时主键冲突：

-- 查询表中最大id值
SELECT MAX(id) FROM sys_role;
-- 查询旧序列的当前值
SELECT last_value FROM sys_role_id_seq;

-- 如果旧序列当前值小于表中最大id，执行更新
SELECT setval('sys_role_id_seq', (SELECT MAX(id) FROM sys_role), true);

3. 将id列绑定到旧序列 #

修改id列的默认值指向旧序列，并更新序列的归属关系：

-- 修改id列的默认值为旧序列的nextval
ALTER TABLE sys_role 
ALTER COLUMN id SET DEFAULT nextval('sys_role_id_seq'::regclass);

-- 将旧序列设置为归属于sys_role.id列
ALTER SEQUENCE sys_role_id_seq OWNED BY sys_role.id;

4. 清理冗余序列 #

解除新序列与表的关联，然后删除它：

-- 解除冗余序列的归属关系
ALTER SEQUENCE sys_role_id_seq1 OWNED BY NONE;

-- 删除冗余序列（确认无其他依赖后执行）
DROP SEQUENCE sys_role_id_seq1;

5. 验证修复结果 #

执行查询验证pg_get_serial_sequence的返回值：

SELECT pg_get_serial_sequence('public.sys_role', 'id');

此时应返回public.sys_role_id_seq，且id列已正确绑定到该序列。

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