PostgreSQL脚本执行时报序列不存在，但nextval可获取序列值的问题

这个问题我之前也碰到过，核心原因是PostgreSQL会在你重命名IDENTITY列的时候自动同步更新对应的序列名称，而你的脚本里在重命名列之后还在用旧的序列名，自然就找不到了。

先给你理清楚来龙去脉：

• 当你用GENERATED BY DEFAULT AS IDENTITY创建audit_study_id_tmp列时，PostgreSQL会自动生成一个序列，命名规则是schema.table_column_seq，也就是logging.audit_study_audit_study_id_tmp_seq。
• 但当你执行RENAME audit_study_id_tmp TO audit_study_id之后，PostgreSQL会自动把这个序列的名字改成logging.audit_study_audit_study_id_seq（跟着列名同步更新）。
• 你的脚本是在重命名列之后才执行PERFORM SETVAL，这时候旧的序列名已经不存在了，所以报错。而你单独执行SELECT nextval的时候，还没执行重命名步骤，所以序列还在，能正常返回结果。

解决方法有两种，选哪种都可以：

方法一：先设置序列值，再重命名列 #

把PERFORM SETVAL移到重命名列之前，这时候旧的序列名还存在，执行完再重命名列，PostgreSQL会自动把序列名同步过去：

DO$$ BEGIN
ALTER TABLE logging.audit_study DROP CONSTRAINT audit_study_pkey, 
DROP COLUMN indication, 
ADD COLUMN indication INT, 
ADD COLUMN audit_study_id_tmp INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
ADD COLUMN aud_action_tmp VARCHAR, 
ADD COLUMN transaction_id_tmp BIGINT DEFAULT (TXID_CURRENT());

UPDATE logging.audit_study SET audit_study_id_tmp = audit_study_id;
UPDATE logging.audit_study SET aud_action_tmp = aud_action;
UPDATE logging.audit_study SET transaction_id_tmp = transaction_id;

-- 先设置序列值，此时序列名还是旧的
PERFORM SETVAL('logging.audit_study_audit_study_id_tmp_seq', (SELECT MAX(audit_study_id_tmp)+1 FROM logging.audit_study), true);

-- 再删除原列并重命名临时列
ALTER TABLE logging.audit_study DROP COLUMN audit_study_id, DROP COLUMN aud_action, DROP COLUMN transaction_id;
ALTER TABLE logging.audit_study RENAME audit_study_id_tmp TO audit_study_id;
ALTER TABLE logging.audit_study RENAME aud_action_tmp TO aud_action;
ALTER TABLE logging.audit_study RENAME transaction_id_tmp TO transaction_id;
END $$

方法二：动态获取序列名（更健壮） #

用pg_get_serial_sequence函数动态获取当前主键列对应的序列名，不管列怎么重命名都不会出错，适合复杂场景：

DO$$ 
DECLARE
    seq_name text;
BEGIN
ALTER TABLE logging.audit_study DROP CONSTRAINT audit_study_pkey, 
DROP COLUMN indication, 
ADD COLUMN indication INT, 
ADD COLUMN audit_study_id_tmp INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
ADD COLUMN aud_action_tmp VARCHAR, 
ADD COLUMN transaction_id_tmp BIGINT DEFAULT (TXID_CURRENT());

UPDATE logging.audit_study SET audit_study_id_tmp = audit_study_id;
UPDATE logging.audit_study SET aud_action_tmp = aud_action;
UPDATE logging.audit_study SET transaction_id_tmp = transaction_id;

-- 删除原列并重命名临时列
ALTER TABLE logging.audit_study DROP COLUMN audit_study_id, DROP COLUMN aud_action, DROP COLUMN transaction_id;
ALTER TABLE logging.audit_study RENAME audit_study_id_tmp TO audit_study_id;
ALTER TABLE logging.audit_study RENAME aud_action_tmp TO aud_action;
ALTER TABLE logging.audit_study RENAME transaction_id_tmp TO transaction_id;

-- 动态获取当前主键列的序列名
seq_name := pg_get_serial_sequence('logging.audit_study', 'audit_study_id');
PERFORM SETVAL(seq_name, (SELECT MAX(audit_study_id)+1 FROM logging.audit_study), true);
END $$

两种方法都能解决你的问题，个人更推荐方法二，因为它不依赖硬编码的序列名，后续如果列名再变动也不用改脚本。

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