我有一个使用枚举类型的表列。我希望更新枚举类型有一个额外的可能值。我不想删除任何现有的值,只是添加新的值。最简单的方法是什么?


当前回答

一个可能的解决方案如下;前提条件是,使用的枚举值中没有冲突。(例如,当删除一个枚举值时,确保该值不再使用。)

-- rename the old enum
alter type my_enum rename to my_enum__;
-- create the new enum
create type my_enum as enum ('value1', 'value2', 'value3');

-- alter all you enum columns
alter table my_table
  alter column my_column type my_enum using my_column::text::my_enum;

-- drop the old enum
drop type my_enum__;

同样,用这种方法列的顺序也不会改变。

其他回答

补充@Dariusz 1

对于Rails 4.2.1,有这样的文档部分:

==事务性迁移

如果数据库适配器支持DDL事务,则所有迁移都将支持 自动包装在事务中。有一些查询 但是不能在事务中执行,对于这些情况 您可以关闭自动事务处理。

class ChangeEnum < ActiveRecord::Migration
  disable_ddl_transaction!

  def up
    execute "ALTER TYPE model_size ADD VALUE 'new_value'"
  end
end

来自Postgres 9.1文档:

ALTER TYPE name ADD VALUE new_enum_value [ { BEFORE | AFTER } existing_enum_value ]

例子:

ALTER TYPE user_status ADD VALUE 'PROVISIONAL' AFTER 'NORMAL'

如上所述,ALTER命令不能写入事务内部。建议的方法是直接插入到pg_enum表中,从pg_type表中检索typelem并计算下一个enumsortorder号;

以下是我使用的代码。检查插入前是否存在重复值(enumtypid和enumlabel名称之间的约束)

INSERT INTO pg_enum (enumtypid, enumlabel, enumsortorder)
    SELECT typelem,
    'NEW_ENUM_VALUE',
    (SELECT MAX(enumsortorder) + 1 
        FROM pg_enum e
        JOIN pg_type p
        ON p.typelem = e.enumtypid
        WHERE p.typname = '_mytypename'
    )
    FROM pg_type p
    WHERE p.typname = '_mytypename'
    AND NOT EXISTS (
        SELECT * FROM 
        pg_enum e
        JOIN pg_type p
        ON p.typelem = e.enumtypid
        WHERE e.enumlabel = 'NEW_ENUM_VALUE'
        AND p.typname = '_mytypename'
    )

注意,在pg_type表中,类型名前面有下划线。此外,where子句中的typname必须全部小写。

现在这可以安全地写入您的db migrate脚本。

我似乎不能发表评论,所以我只能说更新pg_enum在Postgres 8.4中可以工作。对于我们的枚举的设置方式,我已经添加了新的值到现有的枚举类型通过:

INSERT INTO pg_enum (enumtypid, enumlabel)
  SELECT typelem, 'NEWENUM' FROM pg_type WHERE
    typname = '_ENUMNAME_WITH_LEADING_UNDERSCORE';

这有点可怕,但考虑到Postgres实际存储数据的方式,这是有意义的。

免责声明:我没有尝试过这个解决方案,所以它可能不起作用;-)

您应该查看pg_enum。如果你只想改变一个现有ENUM的标签,一个简单的UPDATE就可以做到。

添加一个新的ENUM值。

首先将新值插入到pg_enum中。如果新值必须是最后一个,那么就完成了。 如果不是(你需要一个新的ENUM值在现有的值之间),你将不得不更新你的表中每个不同的值,从最高到最低… 然后你只需要在pg_enum中以相反的顺序重命名它们。

插图 您有以下一组标签:

ENUM ('enum1', 'enum2', 'enum3')

你想要得到:

ENUM ('enum1', 'enum1b', 'enum2', 'enum3')

然后:

INSERT INTO pg_enum (OID, 'newenum3');
UPDATE TABLE SET enumvalue TO 'newenum3' WHERE enumvalue='enum3';
UPDATE TABLE SET enumvalue TO 'enum3' WHERE enumvalue='enum2';

然后:

UPDATE TABLE pg_enum SET name='enum1b' WHERE name='enum2' AND enumtypid=OID;

等等……