为现有ENUM类型添加新值

我有一个使用enum类型的表列。我希望将enum类型更新为具有其他可能的值。我不想删除任何现有的值，只是添加新的值。最简单的方法是什么?

225546 次浏览
小开

最简单的方法:去掉枚举。它们不容易修改，因此应该很少使用非常。

小开

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

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

添加一个新的ENUM值。

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

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;

等等……

小开
最佳答案

请注意如果你使用的是PostgreSQL 9.1或更高版本，并且你可以在事务之外进行更改，请参阅这个答案以获得更简单的方法。

几天前我也遇到了同样的问题，发现了这个帖子。所以我的回答可以帮助那些正在寻找解决方案的人:)

如果只有一两个列使用想要更改的枚举类型，可以尝试这样做。此外，您还可以更改新类型中值的顺序。

-- 1. rename the enum type you want to change
alter type some_enum_type rename to _some_enum_type;
-- 2. create new type
create type some_enum_type as enum ('old', 'values', 'and', 'new', 'ones');
-- 3. rename column(s) which uses our enum type
alter table some_table rename column some_column to _some_column;
-- 4. add new column of new type
alter table some_table add some_column some_enum_type not null default 'new';
-- 5. copy values to the new column
update some_table set some_column = _some_column::text::some_enum_type;
-- 6. remove old column and type
alter table some_table drop column _some_column;
drop type _some_enum_type;

如有多于一栏，应重复3-6。

小开

我似乎不能发表评论，所以我只能说更新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可以工作，就像上面突出显示的中间列技巧一样。你也可以使用USING魔法直接改变列的类型:

CREATE TYPE test AS enum('a', 'b');
CREATE TABLE foo (bar test);
INSERT INTO foo VALUES ('a'), ('b');


ALTER TABLE foo ALTER COLUMN bar TYPE varchar;


DROP TYPE test;
CREATE TYPE test as enum('a', 'b', 'c');


ALTER TABLE foo ALTER COLUMN bar TYPE test
USING CASE
WHEN bar = ANY (enum_range(null::test)::varchar[])
THEN bar::test
WHEN bar = ANY ('{convert, these, values}'::varchar[])
THEN 'c'::test
ELSE NULL
END;

只要没有函数显式地要求或返回该枚举，就没问题。(如果有，pgsql会在删除类型时报错。)

另外，请注意PG9.1引入了一个ALTER TYPE语句，它将对枚举起作用:

http://developer.postgresql.org/pgdocs/postgres/release-9-1-alpha.html

小开

当使用Navicat时，您可以转到类型(在视图下-> others -> types) -获得类型的设计视图-然后单击“添加标签”按钮。

小开

PostgreSQL 9.1引入了改变 Enum类型的能力:

ALTER TYPE enum_type ADD VALUE 'new_value'; -- appends to list
ALTER TYPE enum_type ADD VALUE 'new_value' BEFORE 'old_value';
ALTER TYPE enum_type ADD VALUE 'new_value' AFTER 'old_value';
小开

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

-- 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__;

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

小开

我不知道是否有其他选择，但我们可以使用:

select oid from pg_type where typname = 'fase';'
select * from pg_enum where enumtypid = 24773;'
select * from pg_enum where enumtypid = 24773 and enumsortorder = 6;
delete from pg_enum where enumtypid = 24773 and enumsortorder = 6;
小开

下面是一个更通用但工作相当快的解决方案，除了更改类型本身之外，还使用它更新数据库中的所有列。即使ENUM的新版本不同于多个标签或遗漏了一些原始标签，也可以应用该方法。下面的代码将my_schema.my_type AS ENUM ('a', 'b', 'c')替换为ENUM ('a', 'b', 'd', 'e'):

CREATE OR REPLACE FUNCTION tmp() RETURNS BOOLEAN AS
$BODY$


DECLARE
item RECORD;


BEGIN


-- 1. create new type in replacement to my_type
CREATE TYPE my_schema.my_type_NEW
AS ENUM ('a', 'b', 'd', 'e');


-- 2. select all columns in the db that have type my_type
FOR item IN
SELECT table_schema, table_name, column_name, udt_schema, udt_name
FROM information_schema.columns
WHERE
udt_schema   = 'my_schema'
AND udt_name     = 'my_type'
LOOP
-- 3. Change the type of every column using my_type to my_type_NEW
EXECUTE
' ALTER TABLE ' || item.table_schema || '.' || item.table_name
|| ' ALTER COLUMN ' || item.column_name
|| ' TYPE my_schema.my_type_NEW'
|| ' USING ' || item.column_name || '::text::my_schema.my_type_NEW;';
END LOOP;


-- 4. Delete an old version of the type
DROP TYPE my_schema.my_type;


-- 5. Remove _NEW suffix from the new type
ALTER TYPE my_schema.my_type_NEW
RENAME TO my_type;


RETURN true;


END
$BODY$
LANGUAGE 'plpgsql';


SELECT * FROM tmp();
DROP FUNCTION tmp();

整个过程将运行得相当快，因为如果标签的顺序保持不变，则不会发生实际的数据更改。我用my_type在5个表上应用了这个方法，每个表中有50,000 - 70,000行，整个过程只花了10秒。

当然，如果在数据的某个地方使用了新版本ENUM中缺少的标签，则该函数将返回一个异常，但在这种情况下，无论如何都应该事先做一些事情。

小开

From 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 TABLE foo ALTER COLUMN bar TYPE new_enum_type USING bar::text::new_enum_type失败。我不得不:

ALTER table ALTER COLUMN bar DROP DEFAULT;

然后就成功了。

小开

对于那些寻找事务内解决方案的人来说，下面的方法似乎有用。

而不是ENUM，而是TEXT类型上的DOMAIN应使用，并约束检查该值是否在指定的允许值列表中(如某些注释所建议的)。唯一的问题是，如果一个域被任何复合类型使用，它就不能被添加约束(因此也不能被修改)(文档只是说这“最终应该得到改进”)。但是，可以使用调用函数的约束来解决这样的限制，如下所示。

START TRANSACTION;


CREATE FUNCTION test_is_allowed_label(lbl TEXT) RETURNS BOOL AS $function$
SELECT lbl IN ('one', 'two', 'three');
$function$ LANGUAGE SQL IMMUTABLE;


CREATE DOMAIN test_domain AS TEXT CONSTRAINT val_check CHECK (test_is_allowed_label(value));


CREATE TYPE test_composite AS (num INT, word test_domain);


CREATE TABLE test_table (val test_composite);
INSERT INTO test_table (val) VALUES ((1, 'one')::test_composite), ((3, 'three')::test_composite);
-- INSERT INTO test_table (val) VALUES ((4, 'four')::test_composite); -- restricted by the CHECK constraint


CREATE VIEW test_view AS SELECT * FROM test_table; -- just to show that the views using the type work as expected


CREATE OR REPLACE FUNCTION test_is_allowed_label(lbl TEXT) RETURNS BOOL AS $function$
SELECT lbl IN ('one', 'two', 'three', 'four');
$function$ LANGUAGE SQL IMMUTABLE;


INSERT INTO test_table (val) VALUES ((4, 'four')::test_composite); -- allowed by the new effective definition of the constraint


SELECT * FROM test_view;


CREATE OR REPLACE FUNCTION test_is_allowed_label(lbl TEXT) RETURNS BOOL AS $function$
SELECT lbl IN ('one', 'two', 'three');
$function$ LANGUAGE SQL IMMUTABLE;


-- INSERT INTO test_table (val) VALUES ((4, 'four')::test_composite); -- restricted by the CHECK constraint, again


SELECT * FROM test_view; -- note the view lists the restricted value 'four' as no checks are made on existing data


DROP VIEW test_view;
DROP TABLE test_table;
DROP TYPE test_composite;
DROP DOMAIN test_domain;
DROP FUNCTION test_is_allowed_label(TEXT);


COMMIT;

以前，我使用了一个类似于公认答案的解决方案，但一旦考虑视图、函数或复合类型(特别是使用其他视图的视图，使用修改过的enum…)，它就远远不够好了。这个答案中提出的解决方案似乎在任何条件下都有效。

唯一的缺点是，当删除一些允许的值时，不会对现有数据执行检查(这可能是可以接受的，特别是对于这个问题)。(不幸的是，调用ALTER DOMAIN test_domain VALIDATE CONSTRAINT val_check最终会出现与向复合类型使用的域添加新约束相同的错误。)

请注意，轻微的修改，如__ABC0，其中get_allowed_values()函数返回允许值的列表，将无法工作-这是相当奇怪的，所以我希望上面提出的解决方案可靠地工作(它对我来说，到目前为止…)(它工作，实际上-这是我的错误)

小开

补充@Dariusz 1

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

==事务性迁移

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

class ChangeEnum < ActiveRecord::Migration
disable_ddl_transaction!


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

如果你遇到了应该在事务中添加enum值的情况，例如在ALTER TYPE语句的flyway迁移中执行它，你会得到错误ERROR: ALTER TYPE ... ADD cannot run inside a transaction block(参见飞道350号问题)，你可以直接将这些值添加到pg_enum中作为解决方案(type_egais_units是目标enum的名称):

INSERT INTO pg_enum (enumtypid, enumlabel, enumsortorder)
SELECT 'type_egais_units'::regtype::oid, 'NEW_ENUM_VALUE', ( SELECT MAX(enumsortorder) + 1 FROM pg_enum WHERE enumtypid = 'type_egais_units'::regtype )
小开

如上所述，ALTER命令不能写入事务内部。建议的方法是通过retrieving the typelem from pg_type table和calculating the next enumsortorder number直接插入到pg_enum表中;

以下是我使用的代码。检查插入前是否存在重复值(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脚本。

小开

以防万一，如果你在使用Rails，你有几个语句，你需要一个一个地执行，比如:

execute "ALTER TYPE XXX ADD VALUE IF NOT EXISTS 'YYY';"
execute "ALTER TYPE XXX ADD VALUE IF NOT EXISTS 'ZZZ';"
小开

如果你正在使用Postgres 12(或更高版本)，你可以在transaction (文档)中运行ALTER TYPE ... ADD VALUE。

If ALTER TYPE…ADD VALUE(向枚举中添加新值的表单 类型)在事务块内执行，则新值不能为

因此在迁移中不需要任何hack。

UPD:这里有一个例子(感谢Nick)

ALTER TYPE enum_type ADD VALUE 'new_value';

小开 
DB::statement("ALTER TABLE users DROP CONSTRAINT    users_user_type_check");
$types = ['old_type1', 'old_type1', 'new_type3'];
$result = join( ', ', array_map(function ($value){
return sprintf("'%s'::character varying", $value);
}, $types));
DB::statement("ALTER TABLE users ADD CONSTRAINT users_user_type_check CHECK (user_type::text = ANY    (ARRAY[$result]::text[]))");

提交答案