PostgreSQL更新ENUM类型失败，迁移脚本执行异常求助

问题诊断 #

先拆解两个迁移脚本的核心问题：

变体1的问题 #

• 未处理原有数据中的'error'值：当把status从text转换为新的plugin_status_new时，若表中存在status='error'的记录，会因新ENUM不包含该值直接报错，导致迁移中断。
• 事务包裹逻辑正确，但缺失异常值的转换处理。

变体2的问题 #

• USING子句语法错误：test_agent_plugin_status::plugin_status中的test_agent_plugin_status是表名而非列名，正确写法应为status::plugin_status，这会直接导致SQL执行失败。
• 同样未处理'error'值的转换问题。

你怀疑脚本未整体执行，本质是脚本执行到报错语句就终止，后续步骤自然无法运行。

修正后的迁移脚本 #

针对你的场景，推荐两种可行方案：

方案1：清理异常数据后替换ENUM（兼容所有PostgreSQL版本） #

-- +migrate Up
BEGIN;

-- 先将所有'error'状态的记录更新为有效值，示例改为'uninstalled'
UPDATE test_agent_plugin_status
SET status = 'uninstalled'
WHERE status = 'error';

-- 创建新的ENUM类型
CREATE TYPE plugin_status_new AS ENUM (
    'uninstalled',
    'installing',
    'installed',
    'uninstalling'
);

-- 修改列类型为新ENUM（已清理异常值，可直接转换）
ALTER TABLE test_agent_plugin_status
ALTER COLUMN status TYPE plugin_status_new
USING status::text::plugin_status_new;

-- 删除旧ENUM并重命名新类型
DROP TYPE plugin_status;
ALTER TYPE plugin_status_new RENAME TO plugin_status;

COMMIT;

-- +migrate Down
BEGIN;
-- 回滚时恢复原ENUM结构
CREATE TYPE plugin_status_old AS ENUM (
    'uninstalled',
    'installing',
    'installed',
    'uninstalling',
    'error'
);

ALTER TABLE test_agent_plugin_status
ALTER COLUMN status TYPE plugin_status_old
USING status::text::plugin_status_old;

DROP TYPE plugin_status;
ALTER TYPE plugin_status_old RENAME TO plugin_status;
COMMIT;

方案2：直接修改现有ENUM（PostgreSQL 12+支持） #

若你的PostgreSQL版本为12及以上，可直接删除ENUM中的值，无需创建新类型：

-- +migrate Up
BEGIN;
-- 先清理使用'error'值的记录
UPDATE test_agent_plugin_status
SET status = 'uninstalled'
WHERE status = 'error';

-- 删除ENUM中的'error'值
ALTER TYPE plugin_status DROP VALUE 'error';
COMMIT;

-- +migrate Down
BEGIN;
-- 回滚时添加回'error'值（PostgreSQL添加ENUM值只能追加到末尾）
ALTER TYPE plugin_status ADD VALUE 'error' AFTER 'uninstalling';
COMMIT;

关键注意事项 #

• 必须先处理所有使用待删除ENUM值的记录，否则任何方案都会报错。
• 迁移脚本必须用事务包裹（BEGIN/COMMIT），确保操作原子性，避免数据库处于不一致状态。
• 执行前务必在测试环境验证数据转换逻辑，避免影响生产数据。

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