我有一个使用 enum
类型的表列。我希望更新该 enum
类型以具有其他可能的值。我不想删除任何现有值,只需添加新值。最简单的方法是什么?
PostgreSQL 9.1 引入了 ALTER 枚举类型的功能:
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';
注意如果您使用的是 PostgreSQL 9.1 或更高版本,并且可以在事务之外进行更改,请参阅 this answer 了解更简单的方法。
几天前我遇到了同样的问题,并找到了这篇文章。所以我的回答可能对正在寻找解决方案的人有所帮助:)
如果您只有一两列使用要更改的枚举类型,您可以试试这个。您还可以更改新类型中值的顺序。
-- 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;
如果超过 1 列,则应重复 3-6。
ALTER TYPE
完成所有操作。但即使在此之前,ALTER TABLE foo ALTER COLUMN bar TYPE new_type USING bar::text::new_type;
也远远优于。
ALTER TABLE some_table ALTER COLUMN some_column TYPE some_enum_type USING some_column::text::some_enum_type;
一个可能的解决方案如下;前提是,使用的枚举值没有冲突。 (例如,删除枚举值时,请确保不再使用该值。)
-- 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__;
同样通过这种方式,列顺序不会改变。
ALTER TYPE ... ADD
不同,避免 pg_enum
周围的黑客攻击实际上会破坏事物并且是事务性的。
default for column "my_column" cannot be cast automatically to type "my_enum"
。您必须执行以下操作:ALTER TABLE "my_table" ALTER COLUMN "my_column" DROP DEFAULT, ALTER COLUMN "my_column" TYPE "my_type" USING ("my_column"::text::"my_type"), ALTER COLUMN "my_column" SET DEFAULT 'my_default_value';
如果您遇到应该在事务中添加 enum
值的情况,请在 ALTER TYPE
语句的 flyway 迁移中执行它,您将收到错误 ERROR: ALTER TYPE ... ADD cannot run inside a transaction block
(请参阅 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 )
如果您使用的是 Postgres 12(或更高版本),您可以在事务 (documentation) 中运行 ALTER TYPE ... ADD VALUE
。
如果在事务块内执行 ALTER TYPE ... ADD VALUE(向枚举类型添加新值的形式),则在提交事务之前不能使用新值。
所以迁移中不需要黑客攻击。
UPD:这是一个例子(感谢尼克)
ALTER TYPE enum_type ADD VALUE 'new_value';
ALTER TYPE enum_type ADD VALUE 'new_value';
谢谢!
补充@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 Documentation:
ALTER TYPE name ADD VALUE new_enum_value [ { BEFORE | AFTER } existing_enum_value ]
例子:
ALTER TYPE user_status ADD VALUE 'PROVISIONAL' AFTER 'NORMAL'
以防万一,如果您使用的是 Rails,并且您有多个语句,则需要一一执行,例如:
execute "ALTER TYPE XXX ADD VALUE IF NOT EXISTS 'YYY';"
execute "ALTER TYPE XXX ADD VALUE IF NOT EXISTS 'ZZZ';"
IF NOT EXISTS
位在我的工作中非常宝贵。感谢那。
免责声明:我没有尝试过这个解决方案,所以它可能不起作用;-)
您应该查看 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;
等等...
我似乎无法发表评论,所以我只想说更新 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
ALTER TABLE foo ALTER COLUMN bar TYPE test USING bar::text::new_type;
但现在基本上无关紧要......
... USING bar::type
对我有用。我什至不必指定 ::text
。
无法将评论添加到适当的位置,但在列上使用默认值的 ALTER TABLE foo ALTER COLUMN bar TYPE new_enum_type USING bar::text::new_enum_type
失败。我不得不:
ALTER table ALTER COLUMN bar DROP DEFAULT
;
然后它起作用了。
这是一个更通用但工作速度相当快的解决方案,除了更改类型本身之外,它还会使用它更新数据库中的所有列。即使新版本的 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 中缺少的标签,该函数将返回异常,但在这种情况下无论如何都应该事先做一些事情。
对于那些寻找交易中解决方案的人来说,以下似乎可行。
而不是 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()
函数返回允许值的列表,将不起作用 - 这很奇怪,所以我希望上面提出的解决方案能够可靠地工作(它对我有用,至今...)。
如上所述,ALTER
命令不能写入事务中。建议的方法是直接插入 pg_enum 表,通过 retrieving the typelem from pg_type table
和 calculating the next enumsortorder number
;
以下是我使用的代码。 (在插入之前检查是否存在重复值(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 迁移脚本。
使用 Navicat 时,您可以转到类型(在视图下 -> 其他 -> 类型) - 获取类型的设计视图 - 然后单击“添加标签”按钮。
ERROR: cannot drop type foo because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too.
我不知道是否有其他选择,但我们可以使用以下方法删除该值:
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;
最简单的:摆脱枚举。它们不容易修改,因此应该很少使用。
不定期副业成功案例分享