向现有 ENUM 类型添加新值

Question

我有一个使用 enum 类型的表列。我希望更新该 enum 类型以具有额外的可能值。我不想删除任何现有值，只需添加新值。最简单的方法是什么？

Answer 1

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

Answer 2

注意如果您使用的是 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;

如果多于 1 列，则应重复 3-6。

Answer 3

一个可能的解决方案如下：前提是所使用的枚举值不存在冲突。 （例如，当删除枚举值时，请确保不再使用该值。）

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

同样，通过这种方式，列顺序也不会改变。

Answer 4

如果您使用的是 Postgres 12（或更高版本），您只需在事务内运行 ALTER TYPE ... ADD VALUE (文档）。

如果 ALTER TYPE ... ADD VALUE（向枚举添加新值的形式
type) 在事务块内执行，新值不能被执行
直到事务提交后才使用。

所以迁移中不需要黑客。

UPD：这是一个示例（感谢 Nick）

ALTER TYPE enum_type ADD VALUE 'new_value';

Answer 5

如果您遇到应在事务中添加 enum 值的情况，并在 Flyway 迁移中的 ALTER TYPE 语句上执行它，您将收到错误 ERROR: ALTER TYPE ... ADD 无法在事务块内运行（请参阅 flyway 问题 #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 )

Answer 6

补充@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

Answer 7

以防万一，如果您使用 Rails 并且有多个语句，则需要一一执行，例如：

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

Answer 8

从 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'

Answer 9

免责声明：我还没有尝试过此解决方案，因此它可能不起作用;-)

您应该查看pg_enum。如果您只想更改现有 ENUM 的标签，只需简单的 UPDATE 即可完成。

要添加新的 ENUM 值：

• 首先将新值插入到 pg_enum 中。如果新值必须是最后一个，那么就完成了。
• 如果没有（您需要在现有值之间添加一个新的 ENUM 值），您将必须更新表中的每个不同值，从最高到最低...
• 然后您只需在 < 中重命名它们code>pg_enum 以相反的顺序。

插图
您有以下一组标签：

ENUM ('enum1', 'enum2', 'enum3')

并且您想要获取：

ENUM ('enum1', 'enum1b', 'enum2', 'enum3')

then：

INSERT INTO pg_enum (OID, 'newenum3');
UPDATE TABLE SET enumvalue TO 'newenum3' WHERE enumvalue='enum3';
UPDATE TABLE SET enumvalue TO 'enum3' WHERE enumvalue='enum2';

then：

UPDATE TABLE pg_enum SET name='enum1b' WHERE name='enum2' AND enumtypid=OID;

等等...

Answer 10

我似乎无法发表评论，所以我只想说更新 pg_enum 在 Postgres 8.4 中有效。对于我们的枚举设置方式，我通过以下方式向现有枚举类型添加了新值：

INSERT INTO pg_enum (enumtypid, enumlabel)
  SELECT typelem, 'NEWENUM' FROM pg_type WHERE
    typname = '_ENUMNAME_WITH_LEADING_UNDERSCORE';

这有点可怕，但考虑到 Postgres 实际存储其数据的方式，这是有道理的。

Answer 11

更新 pg_enum 是有效的，就像上面突出显示的中间列技巧一样。人们还可以使用 USING magic 直接更改列的类型：

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

Answer 12

无法将注释添加到适当的位置，但使用列默认值的 ALTER TABLE foo ALTER COLUMN bar TYPE new_enum_type USING bar::text::new_enum_type 失败。我必须：

ALTER table ALTER COLUMN bar DROP DEFAULT;

然后就成功了。

Answer 13

这是一个更通用但运行速度相当快的解决方案，除了更改类型本身之外，它还使用它更新数据库中的所有列。即使新版本的 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 中缺少的标签，该函数将返回异常，但在这种情况下无论如何都应该事先完成一些操作。

Answer 14

对于那些寻找交易内解决方案的人来说，以下方法似乎有效。

应在类型 TEXT 上使用 DOMAIN，而不是 ENUM，并带有检查该值是否在指定的允许值列表内的约束（正如一些评论所建议的那样）。唯一的问题是，如果域被任何复合类型使用，则不能向域添加任何约束（因此也不能修改）（文档只是说“最终应该改进”）。然而，可以通过使用调用函数的约束来解决这种限制，如下所示。

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() 函数返回允许值的列表，将不起作用 - 这很奇怪，所以我希望上面提出的解决方案可靠地工作（到目前为止，它对我来说是有效的......）。（实际上它有效 - 这是我的错误）

Answer 15

如上所述，ALTER 命令不能写入事务内。建议的方法是直接插入到 pg_enum 表中，通过从 pg_type 表中检索 typelem 并计算下一个 enumsortorder 编号；

以下是我使用的代码。 （在插入之前检查是否存在重复值（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 子句中的类型名称需要全部小写。

现在可以这样写安全地进入您的数据库迁移脚本。

Answer 16

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[]))");

Answer 17

使用 Navicat 时，您可以转到类型（在视图 -> 其他 -> 类型下） - 获取类型的设计视图 - 然后单击“添加标签”按钮。

Answer 18

我不知道是否还有其他选择，但我们可以使用以下方法删除该值：

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;

Answer 19

最简单：摆脱枚举。它们不容易修改，因此应该非常很少使用。