我有一个使用枚举类型的表列。我希望更新枚举类型有一个额外的可能值。我不想删除任何现有的值,只是添加新的值。最简单的方法是什么?
当前回答
注意:如果你正在使用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。
其他回答
对于那些寻找事务内解决方案的人来说,下面的方法似乎有用。
不是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最终会得到与向复合类型使用的域添加新约束相同的错误。)
请注意,轻微的修改,如CHECK (value = ANY(get_allowed_values()),其中get_allowed_values()函数返回允许值的列表,将不起作用-这很奇怪,所以我希望上面提出的解决方案可靠地工作(它对我来说,到目前为止…)。(它工作,实际上-这是我的错误)
免责声明:我没有尝试过这个解决方案,所以它可能不起作用;-)
您应该查看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;
等等……
来自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'
最简单的方法:去掉枚举。它们不容易修改,因此应该很少使用。
注意:如果你正在使用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。
推荐文章
- 查询JSON类型内的数组元素
- 获得PostgreSQL数据库中当前连接数的正确查询
- MySQL数据库表中的最大记录数
- 我可以在Java中设置enum起始值吗?
- 从现有模式生成表关系图(SQL Server)
- HyperLogLog算法是如何工作的?
- 纬度和经度的数据类型是什么?
- 数据库和模式的区别
- 如何在PostgreSQL中临时禁用触发器?
- c# vs Java Enum(适合c#新手)
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 如何从命令行在windows中找到mysql数据目录
- 货币应该使用哪种数据类型?