如何删除我在postgresql中创建的枚举类型值？
create type admin_level1 as enum('classifier','moderator','god');
例如.我想从列表中删除主持人.
我似乎无法在文档上找到任何内容.
我正在使用Postgresql 9.3.4.
使用
DROP TYPE删除(删除)任何其他类型的枚举类型：
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;
