logo标签列表

如何在Postgres中删除枚举类型的值？

postgresqlenums
216

我如何在postgresql中删除我创建的枚举类型的值？

create type admin_level1 as enum('classifier', 'moderator', 'god');

例如，我想从列表中移除moderator。

在文档中似乎找不到相关信息。

我正在使用Postgresql 9.3.4。

10个回答
322
你可以像删除其他类型那样使用DROP TYPE命令删除枚举类型：
DROP TYPE admin_level1;

你是否想知道如何从枚举类型中删除单个值？如果是这样，那么很遗憾，无法实现。因为这不被支持：（请参见）:

虽然枚举类型主要用于静态值集，但支持向现有枚举类型添加新值以及重命名值（请参见 ALTER TYPE）。不能从枚举类型中删除现有值，也不能更改这些值的排序顺序，除非删除并重新创建枚举类型。

您必须创建一个新类型，不包含该值，将旧类型的所有现有用途转换为使用新类型，然后删除旧类型。

例如：

CREATE TYPE admin_level1 AS ENUM ('classifier', 'moderator');

CREATE TABLE blah (
    user_id integer primary key,
    power admin_level1 not null
);

INSERT INTO blah(user_id, power) VALUES (1, 'moderator'), (10, 'classifier');

ALTER TYPE admin_level1 ADD VALUE 'god';

INSERT INTO blah(user_id, power) VALUES (42, 'god');

-- .... oops, maybe that was a bad idea

CREATE TYPE admin_level1_new AS ENUM ('classifier', 'moderator');

-- Remove values that won't be compatible with new definition
-- You don't have to delete, you might update instead
DELETE FROM blah WHERE power = 'god';

-- Convert to new type, casting via text representation
ALTER TABLE blah 
  ALTER COLUMN power TYPE admin_level1_new 
    USING (power::text::admin_level1_new);

-- and swap the types
DROP TYPE admin_level1;

ALTER TYPE admin_level1_new RENAME TO admin_level1;
3
这太棒了！有了这个，我成功地解决了Alembic迁移问题。因为(psycopg2.InternalError) ALTER TYPE ... ADD cannot run inside a transaction block的错误，我无法添加新的枚举类型。 - karantan
1
将 disable_ddl_transaction! 添加到迁移文件的顶部。 - chell
2
说实话，我不明白为什么选择了这个答案。这个答案是不正确的！你可以通过指定标签从pg_enum中删除值。 - Roman Podlinov
8
@RomanPoelinov 直接操作目录是有风险的。Postgres之所以不支持原生删除枚举值，是有原因的。与不受支持和不安全的目录操作相比，这种方式怎么可能是“不正确的”呢？ - Craig Ringer
1
PostgreSQL还不支持从枚举类型中删除值吗？ - Thamaraiselvam
显示剩余4条评论
146

这里写得非常好：

http://blog.yo1.dog/updating-enum-values-in-postgresql-the-safe-and-easy-way/

重命名现有类型

ALTER TYPE status_enum RENAME TO status_enum_old;

创建新类型

CREATE TYPE status_enum AS ENUM('queued', 'running', 'done');

更新列以使用新类型

ALTER TABLE job ALTER COLUMN job_status TYPE status_enum USING job_status::text::status_enum;

移除旧类型

DROP TYPE status_enum_old;

可能出现的错误和故障排除:

  • invalid input value for enum {enum name}: "{some value}" - 一个或多个行的值 ("{some value}") 不在新类型中。 在更新列类型之前，您必须处理这些行。
  • default for column "{column_name}" cannot be cast automatically to type {enum_name} - 列的默认值不在新类型中。 您必须在更新列类型之前更改或删除列的默认值。 感谢 Philipp 提供的内容。
  • cannot alter type of a column used by a view or rule - 所有使用该列的视图和规则必须在执行 ALTER 命令之前被删除，然后在之后重新创建。 有自动执行此操作的方法。
请注意：ERROR 25001: ALTER TYPE ... ADD 不能在事务块内运行。 - estrar
4
请注意，如果该列用于约束条件中，您可能会在 <> 上遇到 operator does not exist 错误。我不得不放弃使用该列的检查约束条件，修改该字段，并重新添加约束条件。 - edA-qa mort-ora-y
看起来不错，但我得到了“ERROR：无法将unit_enum_old类型转换为unit_enum”的错误。可能需要进行一些转换。我有146％的把握，没有删除枚举值的值。更新：我的错。在“using”语句中我漏掉了“::text::”。 - Dmitriy Popov
这根本不是解决方案。问题是如何删除枚举值，而不是整个类型。所选的解决方案是正确的。 - flodin
50
这是一个非常危险的操作。你必须确保没有任何表正在使用你想要删除的枚举值。如果没有做到，将会严重破坏所有引用该枚举值的表。
如果你想要删除枚举类型的项，你必须在PostgreSQL的系统表上进行操作。
通过这个命令，你可以显示所有枚举类型的项。
SELECT * FROM pg_enum;

然后检查搜索的值是否唯一。在删除记录时，为了增加唯一性，必须除了'enumlabel'之外还要传递'enumtypid'。
此命令将删除枚举类型中的条目，其中'unique'是您的值。
DELETE FROM pg_enum en WHERE en.enumtypid=124 AND en.enumlabel='unique';

注意：如果数据库中的任何地方使用了'unique'，则绝对不要使用此命令。
27
这是一项非常危险的操作，但如果您知道如何操作，这项操作可以非常快速有效地从枚举类型中删除一个值。首先，请确保没有任何表正在使用您想要删除的枚举值。如果没有，您将严重破坏所有引用该枚举值的表（例如，从这样的表中选择将返回“ERROR：invalid internal value for enum”并且不会返回任何结果）。 - Clint Pachl
7
没错，这是应该考虑的最重要的方面。我所描述的例子必须在以下情况下使用：当我们偶然向枚举类型添加新值但尚未在数据库中使用它时。 - elcudro
1
考虑到这个命令的危险性，DELETE FROM pg_enum en WHERE en.enumtypid=124 AND en.enumlabel='unigue';应该加粗提示注意事项，而不是加粗命令。如果您在某个表中使用了该值，将无法从中恢复。您不能更新包含该值的行，也不能进行转换。唯一的方法是删除整行。 - Sylvain
1
请不要过于吓唬人们。只需确保他们尽力清理所有相关表中的值，并在枚举删除操作之前执行完整的数据库转储（使用pg_dumpall）。因此，如果某个表出现故障，他们可以从转储中手动恢复它。 - SzieberthAdam
1
此外，您可以使用“insert into pg_enum（oid，enumtypid，enumsortorder，enumlabel）values（myoid，myenumtypid，88888888，'* dummy '）;”将虚拟枚举值暂时放回，然后使用“update mytable set myenumcolumn ='mynewvalue' where myenumcolumn =' dummy *';”更新破坏表，最后再使用“delete from pg_enum where oid = myoid;”删除枚举。这里，“myoid”是您从错误消息中获取的已删除枚举值的oid。 - SzieberthAdam
显示剩余3条评论
15

对于那些希望修改枚举值的人来说，重新创建枚举似乎是唯一可行且安全的解决方案。

这个方法包括将枚举列暂时转换为字符串格式，重新创建枚举，然后再将字符串列转换回枚举类型。

以下是一个例子：

ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE varchar(255);
ALTER TABLE your_schema.your_table ALTER COLUMN your_column SET DEFAULT('your_default_enum_value');
DROP TYPE your_schema.your_enum_name;
CREATE TYPE your_schema.your_enum_name AS ENUM ('enum1', 'enum2', 'enum3');
ALTER TABLE your_schema.your_table ALTER your_column DROP DEFAULT;
ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE your_schema.your_enum_name USING your_enum_name::your_schema.your_column;
ALTER TABLE your_schema.your_table ALTER COLUMN your_column SET DEFAULT('your_default_enum_value');
1
ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE your_schema.your_enum_name USING your_enum_name::your_schema.your_column; should be ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE your_schema.your_enum_name USING your_schema.your_column::your_enum_name; - Manuel Darveau
我使用了以下代码： select * from pg_enum where enumlabel='Manager';update pg_enum set enumlabel = 'FacilityManager' where oid=489188; - gerardw
10

使用以下查询从Postgresql类型中删除ENUM值

DELETE FROM pg_enum
WHERE enumlabel = 'moderator'
AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = 'admin_level1');

类型和值的简单说明

DELETE FROM pg_enum
WHERE enumlabel = 'ENUM_VALUE'
AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = 'ENUM_TYPE')

您应该将现有值更改为其他值。如果需要添加新值，则可以使用以下内容：

ALTER TYPE **ENUM_TYPE** ADD VALUE '**ENUM_VALUE2**';

删除之前，将类型值更新为新的类型值或现有值。

唯一的问题是pg_type中的typname是小写的。所以它不起作用，除非在“SELECT oid FROM pg_type WHERE typname ='enum_type'”中使用小写的enum_type。 - fzerorubigd
3
如上所述（https://dev59.com/YF8e5IYBdhLWcg3wYJiu#Rq2gEYcBWogLw_1bpSqJ），这非常危险。 - berkes
4

以下是以程序化方式实现的方法。与https://dev59.com/YF8e5IYBdhLWcg3wYJiu#47305844中给出的相同的总体步骤是适当的，但对于我的目的（编写alembic向下迁移）来说，那些步骤过于繁琐。当然，my_type，my_type_old和value_to_delete应相应更改。

  1. Rename your type.

    ALTER TYPE my_type RENAME TO my_type_old;

  2. Create a new type with the values from your old type, excluding the one you want to delete.

    DO $$
BEGIN
    EXECUTE format(
        'CREATE TYPE my_type AS ENUM (%s)',
        (
            SELECT string_agg(quote_literal(value), ',')
            FROM unnest(enum_range(NULL::my_type_old)) value
            WHERE value <> 'value_to_delete'
        )
    );
END $$;

  3. Change all existing columns which use the old type to use the new one.

    DO $$
DECLARE
    column_data record;
    table_name varchar(255);
    column_name varchar(255);
BEGIN
    FOR column_data IN
        SELECT cols.table_name, cols.column_name
            FROM information_schema.columns cols
            WHERE udt_name = 'my_type_old'
    LOOP
        table_name := column_data.table_name;
        column_name := column_data.column_name;
        EXECUTE format(
            '
                ALTER TABLE %s
                ALTER COLUMN %s
                TYPE my_type
                USING %s::text::my_type;
            ',
            table_name, column_name, column_name
        );
    END LOOP;
END $$;

  4. Delete the old type.

    DROP TYPE my_type_old;
1

无法从ENUM中删除单个值，唯一的解决方案是使用所需的值删除并重新创建ENUM。

1
非常有可能，你可能想表达的是“不受官方支持”。 - Rikudou_Sennin
3
@Rikudou_Sennin，您能否提供一个可以从枚举中删除一个确切值的代码？ - Logovskii Dmitrii
1
我的解决方案始于一个事实，即在我的情况下，我没有权限执行DELETE FROM pg_enum，因为我遇到了权限错误。
从其他回复开始，我创建了一个通用函数，可用于从枚举中删除单个值，支持更新值以释放给定值的使用。
-- https://dev59.com/YF8e5IYBdhLWcg3wYJiu#62444685
-- https://dev59.com/YF8e5IYBdhLWcg3wYJiu#51073579
create or replace function remove_enum_value(
    type_name text,         -- Name of the type where you need to remove a value from
    value_to_delete text,   -- Specific value of the given type you want to remove
    value_fallback text,    -- Which new value columns will have instead of the value deleted
    column_default text     -- DEFAULT value for the column after type alteration (DEFAULT need to be disabled before changing type, https://dev59.com/P1gR5IYBdhLWcg3wlOEr#41149789)
)
RETURNS VOID AS $body$

declare 
    -- Used as temporary type
    _type_name_tmp text := type_name || '_tmp_' || floor(extract(epoch from now()) * 1000);

    -- Used to store statements to execute
    _sql text;

    -- Used to loop tables and switch type from current to temporary
    _column_data record;
    _table_name varchar(255);
    _column_name varchar(255);
   
begin   
    
    --------------------------------------------------------------------------------------------------------------
    
    -- Check: required inputs
    if type_name is null
    then
        raise exception 'Parameter type_name is null';
    end if;
    if value_to_delete is null
    then
        raise exception 'Parameter value_to_delete is null';
    end if;
    
    -- Check: type exists
    IF not EXISTS (SELECT 1 FROM pg_type WHERE typname = type_name) THEN
        raise info 'Type %s does not exists', type_name;
        return;
    END IF;
    
    -- Check: value to delete exists
    if not exists(
        select * 
        FROM pg_enum -- check existing of value to delete
        WHERE enumtypid = (select oid from pg_type where typName=cast(type_name as varchar) limit 1) and enumlabel=cast(value_to_delete as varchar)
    )
    then 
        raise info 'Value to delete % does not exists in type %s', value_to_delete, type_name;
        return;
    end if;
        
    -- Check: fallback value is provided and exists
    if value_fallback is not null and not exists(
        select * 
        FROM pg_enum -- check existing of value to delete
        where
            enumtypid = (select oid from pg_type where typName=cast(type_name as varchar) limit 1)
            and enumlabel=cast(value_fallback as varchar)
    )
    then 
        raise info 'Fallback value % does not exists in type %s', value_fallback, type_name;
        return;
    end if;

    -- Check values are different
    if value_fallback = value_to_delete
    then
        raise info 'Value to delete %s is the same as fallback value %', value_to_delete, value_fallback;
        return;
    end if;

    raise info 'Checks passed, ready to process!';

    --------------------------------------------------------------------------------------------------------------
        
    -- Retrieve current values of type
    _sql := format('
        SELECT string_agg(quote_literal(value), '','')
        FROM unnest(enum_range(NULL::%s)) value
        WHERE value <> ''%s''
    ', type_name, value_to_delete);
    raise info '%', _sql;
    execute _sql into _sql;

    -- Create temporary enum
    _sql := format(
        'CREATE TYPE %s AS ENUM (%s)',
        _type_name_tmp,
        _sql
    );
    raise info '%', _sql;
    execute _sql;

    -- Rename all values from value that need to be deleted to new value (selecting all tables with schemas which has column with enum relation)
    for _column_data in (
        select
            concat(c.table_schema,'.',c.table_name ) as table_name,
            c.column_name
        FROM information_schema.columns c
        where
            c.udt_name = cast(type_name as varchar)
            and c.table_schema=c.udt_schema 
            and data_type = 'USER-DEFINED'
    )
    LOOP
        _sql:= format('UPDATE %1$s set %2$s = %3$L where %2$s=%4$L', _column_data.table_name, _column_data.column_name, value_fallback, value_to_delete);
        raise info 'Update by looping: %', _sql;
        EXECUTE _sql;
    END LOOP;

    -- Switch type from current to temporary
    FOR _column_data in (
        SELECT cols.table_name, cols.column_name
        FROM information_schema.columns cols
        WHERE udt_name = type_name
    )
    LOOP
        _table_name := _column_data.table_name;
        _column_name := _column_data.column_name;
        _sql := format(
            '
                ALTER TABLE %s
                    ALTER COLUMN %s DROP DEFAULT,
                    ALTER COLUMN %s TYPE %s USING %s::text::%s,
                    ALTER COLUMN %s SET DEFAULT %s;
            ',
            _table_name, 
            _column_name, 
            _column_name, _type_name_tmp, _column_name, _type_name_tmp,
            _column_name, (case when column_default is null then null else '''' || column_default || '''::' || _type_name_tmp end)
        );
       
        raise info '%', _sql;
        execute _sql;
    END LOOP;
   
    -- Drop previous type
    _sql := format('DROP TYPE %s;', type_name);
    raise info '%', _sql;
    execute _sql;
    
    -- Rename type to previous name
    _sql := format('ALTER TYPE %s RENAME TO %s;', _type_name_tmp, type_name);
    raise info '%', _sql;
    execute _sql;
       
END $body$
LANGUAGE plpgsql;
0

在v.10的Postgres中遇到了同样的问题。删除需要特定的步骤，如果顺序不正确，甚至会有锁定表进行读取的风险。

编写了一个方便的脚本来进行删除。已经多次证明其性能。但是，此过程涉及将删除的值替换为新值（如果表字段允许，则可以为NULL）。

要使用，只需填写3个值即可。

DO $$
DECLARE
    enumTypeName VARCHAR := 'enum_name'; -- VALUE #1, set yor value!
    enumOldFieldValue varchar := 'old_enum_value'; -- VALUE #2, enum value which have to be deleted
    enumNewFieldValue varchar := null; -- VALUE #3, which new value must be instead of deleted
    sql varchar:='';
    rec record;
BEGIN
    raise info 'Check on old and new enum values.';
    IF exists(select * FROM pg_enum -- check existing of OLD enum value
              WHERE enumtypid = (select oid from pg_type where typName=cast(enumTypeName as varchar) limit 1) and enumlabel=cast(enumOldFieldValue as varchar))
      AND
       (exists(select *
               FROM pg_enum -- check existing of NEW enum value
               WHERE enumtypid = (select oid from pg_type where typName = cast(enumTypeName as varchar) limit 1)
                 and enumlabel = cast(enumNewFieldValue as varchar))
           OR
        enumNewFieldValue IS NULL)
        THEN
            raise info 'Check passed!';

            -- selecting all tables with schemas which has column with enum relation
            create temporary table tmp_table_names
             as SELECT concat(c.table_schema,'.',c.table_name ) as table_name, c.column_name
                FROM information_schema.columns c
                WHERE c.udt_name = cast(enumTypeName as varchar)
                  and c.table_schema=c.udt_schema and data_type = 'USER-DEFINED';

            -- if we have table(s) that uses such enum
            if exists(select * from tmp_table_names)
                then
                    FOR rec in (select table_name, column_name from tmp_table_names) LOOP
                        sql:= format('UPDATE %1$s set %2$s = %3$L where %2$s=%4$L',rec.table_name, rec.column_name, enumNewFieldValue, enumOldFieldValue);
                        raise info 'Update by looping: %', sql;
                        EXECUTE sql;
                    END LOOP;
            end if;

            -- just after changing all old values in all tables we can delete old enum value
            sql := format('DELETE FROM pg_enum WHERE enumtypid = (select oid from pg_type where typName=%1$L limit 1) and enumlabel=%2$L',enumTypeName,enumOldFieldValue);
            raise info 'Delete enum value: %', sql;
            EXECUTE sql;

            drop table  tmp_table_names;
        ELSE
            raise info 'Old or new enum values is missing.';
    end if;
END $$;
  1. 列表项
-1
如果您的数据集不是很大，您可以使用--column-inserts导出，然后用文本编辑器编辑导出的文件，删除值后重新导入该文件。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接