向现有 ENUM 类型添加新值 [英] Adding a new value to an existing ENUM Type

问题描述

我有一个使用 enum 类型的表列.我希望更新该 enum 类型以具有额外的可能值.我不想删除任何现有值，只需添加新值.最简单的方法是什么?

I have a table column that uses an enum type. I wish to update that enum type to have an additional possible value. I don't want to delete any existing values, just add the new value. What is the simplest way to do this?

推荐答案

注意 如果您使用的是 PostgreSQL 9.1 或更高版本，并且您可以在事务之外进行更改，请参阅 这个答案 更简单的方法.

NOTE if you're using PostgreSQL 9.1 or later, and you are ok with making changes outside of a transaction, see this answer for a simpler approach.

几天前我遇到了同样的问题，并找到了这篇文章.所以我的回答对正在寻找解决方案的人有帮助:)

I had the same problem few days ago and found this post. So my answer can be helpful for someone who is looking for solution :)

如果您只有一两列使用要更改的枚举类型，您可以试试这个.您也可以更改新类型中值的顺序.

If you have only one or two columns which use the enum type you want to change, you can try this. Also you can change the order of values in the new type.

-- 1. rename the enum type you want to change
alter type some_enum_type rename to _some_enum_type;
-- 2. create new type
create type some_enum_type as enum ('old', 'values', 'and', 'new', 'ones');
-- 3. rename column(s) which uses our enum type
alter table some_table rename column some_column to _some_column;
-- 4. add new column of new type
alter table some_table add some_column some_enum_type not null default 'new';
-- 5. copy values to the new column
update some_table set some_column = _some_column::text::some_enum_type;
-- 6. remove old column and type
alter table some_table drop column _some_column;
drop type _some_enum_type;

如果多于 1 列，则应重复 3-6.

3-6 should be repeated if there is more than 1 column.

这篇关于向现有 ENUM 类型添加新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！