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

问题描述

我有一个使用枚举类型的表列。我希望更新该枚举类型，使其具有附加的可能值。我不想删除任何现有值，只需添加新值即可。最简单的方法是什么？

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;

3-6。

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