postgresql 如何删除postgres中的枚举类型值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25811017/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete an enum type value in postgres?
提问by Amjith
How do I delete an enum type value that I created in postgresql?
如何删除我在 postgresql 中创建的枚举类型值？
create type admin_level1 as enum('classifier', 'moderator', 'god');
E.g. I want to remove moderatorfrom the list.
例如，我想moderator从列表中删除。
I can't seem to find anything on the docs.
我似乎无法在文档上找到任何内容。
I'm using Postgresql 9.3.4.
我正在使用 Postgresql 9.3.4。
回答by Craig Ringer
You delete (drop) enum types like any other type, with DROP TYPE:
您可以像任何其他类型一样删除（删除）枚举类型，使用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:
您是否实际上是在询问如何从枚举类型中删除单个值？如果是这样，你不能。不支持：
Although
enumtypes are primarily intended for static sets of values, there is support for adding new values to an existing enum type, and for renaming values (seeALTER 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.
尽管
enum类型主要用于静态值集，但支持向现有枚举类型添加新值以及重命名值（请参阅 参考资料ALTER TYPE）。不能从枚举类型中删除现有值，也不能更改此类值的排序顺序，除非删除和重新创建枚举类型。
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.
您必须创建一个没有值的新类型，将旧类型的所有现有用途转换为使用新类型，然后删除旧类型。
E.g.
例如
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;
回答by dnaik
Very well written here:
这里写得很好：
http://blog.yo1.dog/updating-enum-values-in-postgresql-the-safe-and-easy-way/
http://blog.yo1.dog/updating-enum-values-in-postgresql-the-safe-and-easy-way/
rename the existing type
重命名现有类型
ALTER TYPE status_enum RENAME TO status_enum_old;
create the new type
创建新类型
CREATE TYPE status_enum AS ENUM('queued', 'running', 'done');
update the columns to use the new type
更新列以使用新类型
ALTER TABLE job ALTER COLUMN job_status TYPE status_enum USING job_status::text::status_enum;
remove the old type
删除旧类型
DROP TYPE status_enum_old;
回答by elcudro
If you want delete item of enum type, you must operate on system table of PostgreSQL.
如果要删除 enum 类型的项，必须对 PostgreSQL 的系统表进行操作。
With this command, you can display all the items enum type.
使用此命令，您可以显示所有项目的枚举类型。
SELECT * FROM pg_enum;
SELECT * FROM pg_enum;
Then check that searched the value is unique. To increase the uniqueness during the removal of rekoru must be passed 'enumtypid' in addition to 'enumlabel'.
然后检查搜索的值是否唯一。要在删除 rekoru 期间增加唯一性，除了 'enumlabel' 之外，还必须传递 'enumtypid'。
This command removes the entry in enum type, where 'unique' is your value.
此命令删除枚举类型中的条目，其中 'unique' 是您的值。
DELETE FROM pg_enum en WHERE en.enumtypid=124 AND en.enumlabel='unique';
DELETE FROM pg_enum en WHERE en.enumtypid=124 AND en.enumlabel='unique';
NOTEThe example that I described must be used, when by chance we add new value to enum type, and yet we have not used it anywhere in database.
注意我描述的示例必须使用，当我们偶然向枚举类型添加新值时，我们还没有在数据库中的任何地方使用它。
回答by sveilleux2
For those who wish to modify the enum values, recreating it seems to be the only viable and safe solution.
对于那些希望修改枚举值的人来说，重新创建它似乎是唯一可行且安全的解决方案。
It consists in temporarely convert the enum column to a string format, recreate the enum and then reconverting the string column back to the enum type.
它包括暂时将枚举列转换为字符串格式，重新创建枚举，然后将字符串列重新转换回枚举类型。
Here is an example:
下面是一个例子：
ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE varchar(255);
ALTER TABLE your_schema.your_table ALTER COLUMN your_column SET DEFAULT('your_default_enum_value');
DROP TYPE your_schema.your_enum_name;
CREATE TYPE your_schema.your_enum_name AS ENUM ('enum1', 'enum2', 'enum3');
ALTER TABLE your_schema.your_table ALTER your_column DROP DEFAULT;
ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE your_schema.your_enum_name USING your_enum_name::your_schema.your_column;
ALTER TABLE your_schema.your_table ALTER COLUMN your_column SET DEFAULT('your_default_enum_value');
回答by Somnath Muluk
Use following query to Delete ENUM value from Postgresql type
使用以下查询从 Postgresql 类型中删除 ENUM 值
DELETE FROM pg_enum
WHERE enumlabel = 'moderator'
AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = 'admin_level1');
Just info for what's type and what's value
只是关于什么类型和什么价值的信息
DELETE FROM pg_enum
WHERE enumlabel = 'ENUM_VALUE'
AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = 'ENUM_TYPE')
You should change existing values to other. For that if you need to add new value, then use:
您应该将现有值更改为其他值。为此，如果您需要添加新值，请使用：
ALTER TYPE **ENUM_TYPE** ADD VALUE '**ENUM_VALUE2**';
Before deleting, update type value to new type value or existing value.
在删除之前，将类型值更新为新类型值或现有值。
回答by Californian
The programmatic way to do this is as follows. The same general steps as given
in https://stackoverflow.com/a/47305844/629272are appropriate, but those are
more manual than made sense for my purposes (writing an alembic down migration). my_type, my_type_old, and value_to_delete, should, of course, be changed as appropriate.
执行此操作的编程方式如下。与https://stackoverflow.com/a/47305844/629272中给出的相同的一般步骤是合适的，但对于我的目的（编写 alembic down 迁移）而言，这些步骤比手动操作更有意义。my_type, my_type_old, 和value_to_delete, 当然应该适当更改。
Rename your type.
ALTER TYPE my_type RENAME TO my_type_old;Create a new type with the values from your old type, excluding the one you want to delete.
DO $$ BEGIN EXECUTE format( 'CREATE TYPE my_type AS ENUM (%s)', ( SELECT string_agg(quote_literal(value), ',') FROM unnest(enum_range(NULL::my_type_old)) value WHERE value <> 'value_to_delete' ) ); END $$;Change all existing columns which use the old type to use the new one.
DO $$ DECLARE column_data record; table_name varchar(255); column_name varchar(255); BEGIN FOR column_data IN SELECT cols.table_name, cols.column_name FROM information_schema.columns cols WHERE udt_name = 'my_type_old' LOOP table_name := column_data.table_name; column_name := column_data.column_name; EXECUTE format( ' ALTER TABLE %s ALTER COLUMN %s TYPE my_type USING %s::text::my_type; ', table_name, column_name, column_name ); END LOOP; END $$;Delete the old type.
DROP TYPE my_type_old;
重命名您的类型。
ALTER TYPE my_type RENAME TO my_type_old;使用旧类型中的值创建一个新类型，不包括要删除的值。
DO $$ BEGIN EXECUTE format( 'CREATE TYPE my_type AS ENUM (%s)', ( SELECT string_agg(quote_literal(value), ',') FROM unnest(enum_range(NULL::my_type_old)) value WHERE value <> 'value_to_delete' ) ); END $$;将所有使用旧类型的现有列更改为使用新类型。
DO $$ DECLARE column_data record; table_name varchar(255); column_name varchar(255); BEGIN FOR column_data IN SELECT cols.table_name, cols.column_name FROM information_schema.columns cols WHERE udt_name = 'my_type_old' LOOP table_name := column_data.table_name; column_name := column_data.column_name; EXECUTE format( ' ALTER TABLE %s ALTER COLUMN %s TYPE my_type USING %s::text::my_type; ', table_name, column_name, column_name ); END LOOP; END $$;删除旧类型。
DROP TYPE my_type_old;
回答by sherpya
if your dataset is not so big you can dump with --column-insertsedit the dump with a text editor, remove the value and re-import the dump
如果您的数据集不是那么大，您可以使用--column-inserts文本编辑器编辑转储进行转储，删除该值并重新导入转储
回答by Zaytsev Dmitry
It's not possible to delete individual value from ENUM, the only possible solution is to DROP and recreate ENUM with needed values.
从 ENUM 中删除单个值是不可能的，唯一可能的解决方案是删除并重新创建具有所需值的 ENUM。
