PostgreSQL - 更新枚举类型 [英] PostgreSQL - Updating an Enum Type

问题描述

我有一个使用枚举类型的字段。我想更新枚举有一个额外的字段（我不想删除任何东西，只需添加一个新的标签）。最简单的方法是什么？

解决方案

注意如果您使用PostgreSQL 9.1或稍后，请参阅此答案以获得更简单的方法。

---

几天前我有同样的问题，发现这个帖子。所以我的答案可以帮助一个正在寻找解决方案的人:)

如果您只有一列或两列使用要更改的枚举类型，则可以尝试这个。另外，您可以更改新类型中的值顺序。

   -  1.重命名要更改的枚举类型
 alter type some_enum_type重命名为_some_enum_type; 
  -  2.创建新类型
创建类型some_enum_type作为枚举（'old'，'values'，'和'，'new'，'ones'）; 
  -  3.重命名列使用我们的枚举类型
 alter table some_table rename column some_column to _some_column; 
  -  4.添加新列的新类型
 alter table some_table add some_column some_enum_type not null default'new'; 
  -  5.将值复制到新列
更新some_table set some_column = _some_column :: text :: some_enum_type; 
  -  6.删除旧列并键入
 alter table some_table drop column _some_column; 
 drop type _some_enum_type; 
  

3-6应该重复，如果有超过1列。

I've got a field that is using an enumeration type. I wish to update the enum to have an additional field (I don't want to delete anything, just add a new label). What is the simplest way to do this?

解决方案

NOTE if you're using PostgreSQL 9.1 or later, 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 should be repeated if there is more than 1 column.

这篇关于PostgreSQL - 更新枚举类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！