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

可以像删除其他类型一样删除(删除)枚举类型，使用 DROP TYPE:

DROP TYPE admin_level1;

有没有可能你实际上是在问如何使用 从枚举类型中删除单个值? 如果是这样，你就不能:

虽然 enum类型主要用于静态值集，但是支持向现有枚举类型添加新值和重命名值(参见 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;

如果要删除枚举类型的项，则必须对 PostgreSQL 的系统表进行操作。

使用此命令，可以显示所有项枚举类型。

选择 * FROM pg _ enum;

然后检查搜索的值是否唯一。为了在移除 rekoru 时增加唯一性，除了传递“枚举标签”外，还必须传递“枚举类型”。

此命令将删除枚举类型中的条目，其中“ only”是您的值。

DELETE FROM pg _ enum en WHERE en.枚举标签 = 124 AND en.枚举标签 = ‘ unique’;

注意 我所描述的示例必须使用，当偶然地向枚举类型添加新值时，但是我们还没有在数据库中的任何地方使用它。

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

它包括暂时将枚举列转换为字符串格式，重新创建枚举，然后将字符串列重新转换为枚举类型。

这里有一个例子:

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

如果您的数据集不太大，您可以使用 --column-inserts转储，那么使用文本编辑器编辑转储，删除该值并重新导入转储

使用以下查询从 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**';

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

这里写得很好:

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}-列的默认值不在新类型中。在更新列类型之前，必须更改或移除列的默认值。感谢菲利普增加了这个项目。
• cannot alter type of a column used by a view or rule-在执行 ALTER 之前，必须删除使用该列的所有视图和规则，然后再重新创建它们。有很多方法可以自动完成。

实现此目的的编程方法如下 在 https://stackoverflow.com/a/47305844/629272是合适的，但这些是 更多的是手动的，而不是对我的目的有意义的(写一个向下的迁移)。当然，my_type、 my_type_old和 value_to_delete应该作适当的修改。

1. 重命名类型。

   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;
   

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

在第10节也有同样的问题。 删除需要特定的过程，如果顺序不正确，那么甚至有可能锁定表以进行读取。

写了一个方便删除的脚本。已经证明了它的性能好几倍。但是，这个过程涉及到用一个新值替换已删除的值(如果表字段允许，它可以为 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. 列表项目

我的解决方案开始于这样一个事实，即在我的方案中没有执行 DELETE FROM pg_enum的权限，因为我得到了一个权限错误。

从其他答复开始，我创建了一个泛型函数，可用于从枚举中删除单个值，支持更新值以释放给定值的使用

-- https://stackoverflow.com/a/62444685
-- https://stackoverflow.com/a/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://stackoverflow.com/a/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;