我有一个使用枚举类型的表列。我希望更新枚举类型有一个额外的可能值。我不想删除任何现有的值,只是添加新的值。最简单的方法是什么?
当前回答
来自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'
其他回答
下面是一个更通用但工作相当快的解决方案,除了更改类型本身之外,还使用它更新数据库中的所有列。即使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中缺少的标签,则该函数将返回一个异常,但在这种情况下,无论如何都应该事先做一些事情。
如果你遇到这样的情况,你应该在事务中添加枚举值,例如在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 )
当使用Navicat时,您可以转到类型(在视图下-> others -> types) -获得类型的设计视图-然后单击“添加标签”按钮。
更新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
对于那些寻找事务内解决方案的人来说,下面的方法似乎有用。
不是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()函数返回允许值的列表,将不起作用-这很奇怪,所以我希望上面提出的解决方案可靠地工作(它对我来说,到目前为止…)。(它工作,实际上-这是我的错误)
推荐文章
- 查询JSON类型内的数组元素
- 获得PostgreSQL数据库中当前连接数的正确查询
- MySQL数据库表中的最大记录数
- 我可以在Java中设置enum起始值吗?
- 从现有模式生成表关系图(SQL Server)
- HyperLogLog算法是如何工作的?
- 纬度和经度的数据类型是什么?
- 数据库和模式的区别
- 如何在PostgreSQL中临时禁用触发器?
- c# vs Java Enum(适合c#新手)
- 输入文件似乎是一个文本格式转储。请使用psql
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 如何从命令行在windows中找到mysql数据目录
- 货币应该使用哪种数据类型?