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

DROP TYPE admin_level1;

您是否有可能实际上询问如何从枚举类型中删除单个值？如果是这样,你就不能.它不受支持.您必须创建没有值的新类型,将旧类型的所有现有用法转换为使用新类型,然后删除旧类型.

例如.

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;