database 向现有 ENUM 类型添加新值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1771543/
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
Adding a new value to an existing ENUM Type
提问by Ian
I have a table column that uses an enumtype. I wish to update that enumtype 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?
我有一个使用enum类型的表列。我希望更新该enum类型以获得额外的可能值。我不想删除任何现有值，只需添加新值。什么是最简单的方法来做到这一点？
采纳答案by taksofan
NOTEif you're using PostgreSQL 9.1 or later, and you are ok with making changes outside of a transaction, see this answerfor a simpler approach.
注意如果您使用的是 PostgreSQL 9.1 或更高版本，并且您可以在事务之外进行更改，请参阅此答案以获取更简单的方法。
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.
如果多于 1 列，则应重复 3-6。
回答by Dariusz
回答by Steffen
A possible solution is the following; precondition is, that there are not conflicts in the used enum values. (e.g. when removing an enum value, be sure that this value is not used anymore.)
一个可能的解决方案如下；前提是，使用的枚举值没有冲突。（例如，删除枚举值时，请确保不再使用该值。）
-- rename the old enum
alter type my_enum rename to my_enum__;
-- create the new enum
create type my_enum as enum ('value1', 'value2', 'value3');
-- alter all you enum columns
alter table my_table
alter column my_column type my_enum using my_column::text::my_enum;
-- drop the old enum
drop type my_enum__;
Also in this way the column order will not be changed.
同样以这种方式，列顺序不会改变。
回答by Hubbitus
If you fall into situation when you should add enumvalues in transaction, f.e. execute it in flyway migration on ALTER TYPEstatement you will be get error ERROR: ALTER TYPE ... ADD cannot run inside a transaction block(see flyway issue #350) you could add such values into pg_enumdirectly as workaround (type_egais_unitsis name of target enum):
如果您遇到应该enum在事务中添加值的情况，fe 在 flyway migration onALTER TYPE语句中执行它，您将收到错误ERROR: ALTER TYPE ... ADD cannot run inside a transaction block（请参阅flyway 问题 #350），您可以将这些值pg_enum直接添加到解决方法中（type_egais_units是目标名称enum）：
INSERT INTO pg_enum (enumtypid, enumlabel, enumsortorder)
SELECT 'type_egais_units'::regtype::oid, 'NEW_ENUM_VALUE', ( SELECT MAX(enumsortorder) + 1 FROM pg_enum WHERE enumtypid = 'type_egais_units'::regtype )
回答by Kiko Castro
Complementing @Dariusz 1
补充@Dariusz 1
For Rails 4.2.1, there's this doc section:
对于 Rails 4.2.1，有这个文档部分：
== Transactional Migrations
== 事务性迁移
If the database adapter supports DDL transactions, all migrations will automatically be wrapped in a transaction. There are queries that you can't execute inside a transaction though, and for these situations you can turn the automatic transactions off.
如果数据库适配器支持 DDL 事务，则所有迁移都将自动包装在一个事务中。但是，有些查询无法在事务内执行，对于这些情况，您可以关闭自动事务。
class ChangeEnum < ActiveRecord::Migration
disable_ddl_transaction!
def up
execute "ALTER TYPE model_size ADD VALUE 'new_value'"
end
end
回答by Peymankh
From Postgres 9.1 Documentation:
来自 Postgres 9.1文档：
ALTER TYPE name ADD VALUE new_enum_value [ { BEFORE | AFTER } existing_enum_value ]
Example:
例子：
ALTER TYPE user_status ADD VALUE 'PROVISIONAL' AFTER 'NORMAL'
回答by benja
Disclaimer:I haven't tried this solution, so it might not work ;-)
免责声明：我还没有尝试过这个解决方案，所以它可能不起作用;-)
You should be looking at pg_enum. If you only want to change the label of an existing ENUM, a simple UPDATE will do it.
你应该看看pg_enum. 如果您只想更改现有 ENUM 的标签，一个简单的 UPDATE 即可完成。
To add a new ENUM values:
添加新的 ENUM 值：
- First insert the new value into
pg_enum. If the new value has to be the last, you're done. - If not (you need to a new ENUM value in between existing ones), you'll have to update each distinct value in your table, going from the uppermost to the lowest...
- Then you'll just have to rename them in
pg_enumin the opposite order.
- 首先将新值插入到
pg_enum. 如果新值必须是最后一个，那么您就完成了。 - 如果不是（您需要在现有的 ENUM 值之间添加一个新的 ENUM 值），则必须更新表中的每个不同值，从最高到最低...
- 然后你只需要以
pg_enum相反的顺序重命名它们。
Illustration
You have the following set of labels:
插图
您有以下标签集：
ENUM ('enum1', 'enum2', 'enum3')
and you want to obtain:
并且您想获得：
ENUM ('enum1', 'enum1b', 'enum2', 'enum3')
then:
然后：
INSERT INTO pg_enum (OID, 'newenum3');
UPDATE TABLE SET enumvalue TO 'newenum3' WHERE enumvalue='enum3';
UPDATE TABLE SET enumvalue TO 'enum3' WHERE enumvalue='enum2';
then:
然后：
UPDATE TABLE pg_enum SET name='enum1b' WHERE name='enum2' AND enumtypid=OID;
And so on...
等等...
回答by Josiah
I can't seem to post a comment, so I'll just say that updating pg_enum works in Postgres 8.4 . For the way our enums are set up, I've added new values to existing enum types via:
我似乎无法发表评论，所以我只想说更新 pg_enum 在 Postgres 8.4 中有效。对于我们枚举的设置方式，我通过以下方式向现有的枚举类型添加了新值：
INSERT INTO pg_enum (enumtypid, enumlabel)
SELECT typelem, 'NEWENUM' FROM pg_type WHERE
typname = '_ENUMNAME_WITH_LEADING_UNDERSCORE';
It's a little scary, but it makes sense given the way Postgres actually stores its data.
这有点吓人，但考虑到 Postgres 实际存储其数据的方式，这是有道理的。
回答by Denis de Bernardy
Updating pg_enum works, as does the intermediary column trick highlighted above. One can also use USING magic to change the column's type directly:
更新 pg_enum 有效，就像上面突出显示的中间列技巧一样。还可以使用 USING magic 直接更改列的类型：
CREATE TYPE test AS enum('a', 'b');
CREATE TABLE foo (bar test);
INSERT INTO foo VALUES ('a'), ('b');
ALTER TABLE foo ALTER COLUMN bar TYPE varchar;
DROP TYPE test;
CREATE TYPE test as enum('a', 'b', 'c');
ALTER TABLE foo ALTER COLUMN bar TYPE test
USING CASE
WHEN bar = ANY (enum_range(null::test)::varchar[])
THEN bar::test
WHEN bar = ANY ('{convert, these, values}'::varchar[])
THEN 'c'::test
ELSE NULL
END;
As long as you've no functions that explicitly require or return that enum, you're good. (pgsql will complain when you drop the type if there are.)
只要您没有明确要求或返回该枚举的函数，就很好。（如果有的话，当你删除类型时，pgsql 会抱怨。）
Also, note that PG9.1 is introducing an ALTER TYPE statement, which will work on enums:
另外，请注意 PG9.1 引入了一个 ALTER TYPE 语句，它将适用于枚举：
http://developer.postgresql.org/pgdocs/postgres/release-9-1-alpha.html
http://developer.postgresql.org/pgdocs/postgres/release-9-1-alpha.html
回答by Denis de Bernardy
Simplest: get rid of enums. They are not easily modifiable, and thus should veryrarely be used.
最简单：摆脱枚举。他们是不会轻易改变的，因此应该非常很少被使用。
