postgresql - 如何删除 postgres 中的枚举类型值？

Question

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

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

例如我想从列表中删除 moderator。

我似乎无法在文档中找到任何内容。

我正在使用 Postgresql 9.3.4。

Answer 1

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

DROP TYPE admin_level1;

---

您是否真的在询问如何从枚举类型中删除单个值？如果是这样，你不能。 It's not supported :

Although enum types are primarily intended for static sets of values, there is support for adding new values to an existing enum type, and for renaming values (see ALTER TYPE). Existing values cannot be removed from an enum type, nor can the sort ordering of such values be changed, short of dropping and re-creating the enum 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;