我有一个使用枚举类型的表列。我希望更新枚举类型有一个额外的可能值。我不想删除任何现有的值,只是添加新的值。最简单的方法是什么?
当前回答
下面是一个更通用但工作相当快的解决方案,除了更改类型本身之外,还使用它更新数据库中的所有列。即使ENUM的新版本不同于多个标签或遗漏了一些原始标签,也可以应用该方法。下面的代码替换my_schema。my_type ENUM (a, b, c)与枚举(“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中缺少的标签,则该函数将返回一个异常,但在这种情况下,无论如何都应该事先做一些事情。
其他回答
我似乎不能发表评论,所以我只能说更新pg_enum在Postgres 8.4中可以工作。对于我们的枚举的设置方式,我已经添加了新的值到现有的枚举类型通过:
INSERT INTO pg_enum (enumtypid, enumlabel)
SELECT typelem, 'NEWENUM' FROM pg_type WHERE
typname = '_ENUMNAME_WITH_LEADING_UNDERSCORE';
这有点可怕,但考虑到Postgres实际存储数据的方式,这是有意义的。
如果你遇到这样的情况,你应该在事务中添加枚举值,例如在ALTER TYPE语句的flyway migration中执行它,你将得到错误error: ALTER TYPE…ADD不能在事务块中运行(参见flyway issue #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 )
注意:如果你正在使用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。
一个可能的解决方案如下;前提条件是,使用的枚举值中没有冲突。(例如,当删除一个枚举值时,确保该值不再使用。)
-- 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__;
同样,用这种方法列的顺序也不会改变。
如果你正在使用Postgres 12(或更高版本),你可以运行ALTER TYPE…在交易(文档)中增加价值。
如果ALTER TYPE…ADD VALUE(向枚举中添加新值的表单 类型)在事务块内执行,则新值不能为 一直使用到事务提交之后。
因此在迁移中不需要任何hack。
UPD:这里有一个例子(感谢Nick)
添加一个新值
推荐文章
- 查询JSON类型内的数组元素
- 获得PostgreSQL数据库中当前连接数的正确查询
- MySQL数据库表中的最大记录数
- 我可以在Java中设置enum起始值吗?
- 从现有模式生成表关系图(SQL Server)
- HyperLogLog算法是如何工作的?
- 纬度和经度的数据类型是什么?
- 数据库和模式的区别
- 如何在PostgreSQL中临时禁用触发器?
- c# vs Java Enum(适合c#新手)
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 如何从命令行在windows中找到mysql数据目录
- 货币应该使用哪种数据类型?