如何通过ActiveRecord迁移替换PostgreSQL枚举类型？

PostgreSQL不支持直接修改现有枚举的成员，得通过临时枚举过渡的方式完成替换，具体迁移步骤如下：

迁移代码实现 #

先创建新迁移文件（比如执行rails generate migration UpdateSalaryTypesEnum），然后将迁移内容替换为：

class UpdateSalaryTypesEnum < ActiveRecord::Migration[7.0]
  def up
    # 1. 创建临时枚举类型
    execute <<~SQL
      CREATE TYPE salary_types_temp AS ENUM ('good', 'medium', 'low', 'bad');
    SQL

    # 2. 映射旧枚举值到新值，同时切换字段使用临时枚举
    execute <<~SQL
      ALTER TABLE salaries
        ALTER COLUMN salary_type TYPE salary_types_temp
        USING CASE salary_type
          WHEN 'very_good' THEN 'good'::salary_types_temp
          WHEN 'is_medium' THEN 'medium'::salary_types_temp
          WHEN 'too_low' THEN 'low'::salary_types_temp
          WHEN 'BAD' THEN 'bad'::salary_types_temp
        END;
    SQL

    # 3. 删除旧枚举类型
    execute <<~SQL
      DROP TYPE salary_types;
    SQL

    # 4. 创建目标枚举类型
    execute <<~SQL
      CREATE TYPE salary_types AS ENUM ('good', 'medium', 'low', 'bad');
    SQL

    # 5. 切换字段回正式枚举，删除临时枚举
    execute <<~SQL
      ALTER TABLE salaries
        ALTER COLUMN salary_type TYPE salary_types
        USING salary_type::text::salary_types;
    SQL

    execute <<~SQL
      DROP TYPE salary_types_temp;
    SQL
  end

  def down
    # 回滚逻辑：恢复旧枚举和对应值
    execute <<~SQL
      CREATE TYPE salary_types_temp AS ENUM ('very_good', 'is_medium', 'too_low', 'BAD');
    SQL

    execute <<~SQL
      ALTER TABLE salaries
        ALTER COLUMN salary_type TYPE salary_types_temp
        USING CASE salary_type
          WHEN 'good' THEN 'very_good'::salary_types_temp
          WHEN 'medium' THEN 'is_medium'::salary_types_temp
          WHEN 'low' THEN 'too_low'::salary_types_temp
          WHEN 'bad' THEN 'BAD'::salary_types_temp
        END;
    SQL

    execute <<~SQL
      DROP TYPE salary_types;
    SQL

    execute <<~SQL
      CREATE TYPE salary_types AS ENUM ('very_good', 'is_medium', 'too_low', 'BAD');
    SQL

    execute <<~SQL
      ALTER TABLE salaries
        ALTER COLUMN salary_type TYPE salary_types
        USING salary_type::text::salary_types;
    SQL

    execute <<~SQL
      DROP TYPE salary_types_temp;
    SQL
  end
end

关键说明 #

• 用临时枚举过渡是因为PostgreSQL不允许直接修改枚举成员列表，必须先替换字段关联的枚举类型，才能删除旧枚举
• USING子句负责旧值到新值的映射，要确保每个旧枚举值都有对应新值，避免数据丢失
• down方法做完全反向操作，保证迁移可以安全回退

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