如何删除postgres中的枚举类型值？ [英] How to delete an enum type value in postgres?

问题描述

如何删除我在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中的枚举类型值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！