重命名 PostgreSQL 中的枚举项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12627830/
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
Rename enum item in PostgreSQL
提问by David S
I would like to change the name of an item in an enum type in PostgreSQL 9.1.5.
我想在 PostgreSQL 9.1.5 中更改枚举类型中项目的名称。
Here is the type's create stmt:
这是该类型的创建 stmt：
CREATE TYPE import_action AS ENUM
('Ignored',
'Inserted',
'Updated',
'Task created');
I just want to change 'Task created' to 'Aborted'. It seems like from the documentation, that the following should work:
我只想将“创建的任务”更改为“中止”。从文档看来，以下应该有效：
ALTER TYPE import_action
RENAME ATTRIBUTE "Task created" TO "Aborted";
However, I get a msg:
但是，我收到一条消息：
********** Error **********
ERROR: relation "import_action" does not exist
SQL state: 42P01
But, it clearly does exist.
但是，它显然确实存在。
The type is currently being used by more than one table.
该类型当前被多个表使用。
I'm being to think that there must not be a way to do this. I've tried the dialog for the type in pgAdminIII, but there is no way that I can see to rename the it there. (So, either a strong hint that I can't do it, or - I'm hoping - a small oversight be the developer that created that dialog)
我认为一定没有办法做到这一点。我已经在 pgAdminIII 中尝试了该类型的对话框，但是我无法看到在那里重命名它。（所以，要么强烈暗示我不能这样做，要么 - 我希望 - 创建该对话框的开发人员是一个小小的疏忽）
If I can't do this in one statment? Then what do I need to do? Will I have to write a script to add the item, update all of the records to new value, then drop the old item? Will that even work?
如果我不能在一份声明中做到这一点？那我需要做什么？我是否必须编写脚本来添加项目，将所有记录更新为新值，然后删除旧项目？这甚至会起作用吗？
It's seems like this should be a simple thing. As I understand it, the records are just storing a reference to the type and item. I don't think they are actually store the text value that I have given it. But, maybe I'm wrong here as well.
看起来这应该是一件很简单的事情。据我了解，记录只是存储对类型和项目的引用。我不认为它们实际上存储了我给它的文本值。但是，也许我在这里也错了。
采纳答案by Dondi Michael Stroma
Names of enum values are called labels, attributes are something different entirely.
枚举值的名称称为标签，属性则完全不同。
Unfortunately changing enum labels is not simple, you have to muck with the system catalog: http://www.postgresql.org/docs/9.1/static/catalog-pg-enum.html
不幸的是，更改枚举标签并不简单，您必须使用系统目录：http: //www.postgresql.org/docs/9.1/static/catalog-pg-enum.html
UPDATE pg_enum SET enumlabel = 'Aborted'
WHERE enumlabel = 'Task created' AND enumtypid = (
SELECT oid FROM pg_type WHERE typname = 'import_action'
)
回答by cstroe
In PostgreSQL version 10, the ability to rename the labels of an enum has been addedas part of the ALTER TYPEsyntax:
在 PostgreSQL 版本 10 中，重命名枚举标签的功能已添加为ALTER TYPE语法的一部分：
ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value
回答by EM0
The query in the accepted answer doesn't take into account schema names. Here's a safer (and simpler) one, based on http://tech.valgog.com/2010/08/alter-enum-in-postgresql.html
接受的答案中的查询不考虑架构名称。这是一个更安全（更简单）的，基于http://tech.valgog.com/2010/08/alter-enum-in-postgresql.html
UPDATE pg_catalog.pg_enum
SET enumlabel = 'NEW_LABEL'
WHERE enumtypid = 'SCHEMA_NAME.ENUM_NAME'::regtype::oid AND enumlabel = 'OLD_LABEL'
RETURNING enumlabel;
Note that this requires the "rolcatupdate" (Update catalog directly) permission - even being a superuser is not enough.
请注意，这需要“rolcatupdate”（直接更新目录）权限——即使是超级用户也是不够的。
It seems that updating the catalog directly is still the only way as of PostgreSQL 9.3.
从 PostgreSQL 9.3 开始，似乎直接更新目录仍然是唯一的方法。
回答by Mike Sherrill 'Cat Recall'
There's a difference between types, attributes, and values. You can create an enum like this.
类型、属性和值之间存在差异。您可以像这样创建一个枚举。
CREATE TYPE import_action AS ENUM
('Ignored',
'Inserted',
'Updated',
'Task created');
Having done that, you can add valuesto the enum.
做完那，你可以添加值的枚举。
ALTER TYPE import_action
ADD VALUE 'Aborted';
But the syntax diagram doesn't show any support for dropping or renaming a value. The syntax you were looking at was the syntax for renaming an attribute, not a value.
但是语法图没有显示对删除或重命名value 的任何支持。您正在查看的语法是重命名属性的语法，而不是值。
Although this design is perhaps surprising, it's also deliberate. From the pgsql-hackers mailing list.
尽管这种设计可能令人惊讶，但也是经过深思熟虑的。来自pgsql-hackers 邮件列表。
If you need to modify the values used or want to know what the integer is, use a lookup table instead. Enums are the wrong abstraction for you.
如果您需要修改使用的值或想知道整数是什么，请改用查找表。枚举对你来说是错误的抽象。
