如何删除我在postgresql中创建的枚举类型值?创建类型admin_level1作为枚举('classifier','主持人','god');
How do I delete an enum type value that I created in postgresql?
create type admin_level1 as enum('classifier', 'moderator', 'god');例如。我想从列表中删除主持人。
E.g. I want to remove moderator from the list.
我似乎没有找到任何文档。
I can't seem to find anything on the docs.
我正在使用Postgresql 9.3.4。
I'm using Postgresql 9.3.4.
推荐答案你删除(删除)枚举类型,如任何其他类型, DROP TYPE :
You delete (drop) enum types like any other type, with DROP TYPE:
DROP TYPE admin_level1;
有可能你实际询问从从枚举类型中删除单个值?如果是这样,你不能。不支持您必须创建一个没有值的新类型,将旧类型的所有现有使用转换为使用新类型,然后删除旧类型。
Is it possible you're actually asking about how to remove an individual value from an enum type? If so, you can't. It's not supported. You must create a new type without the value, convert all existing uses of the old type to use the new type, then drop the old 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;更多推荐
如何删除postgres中的枚举类型值?
发布评论