PostgreSQL 插入到枚举数组中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18234946/
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
PostgreSQL INSERT into an array of enums
提问by Zapnologica
How can I insert an array of enums?
Here is my enum:
如何插入array of enums?
这是我的enum：
CREATE TYPE equipment AS ENUM ('projector','PAsystem','safe','PC','phone');
Then my table has an array of equipment:
然后我的桌子上有一系列设备：
CREATE TABLE lecture_room (
id INTEGER DEFAULT NEXTVAL('lecture_id_seq')
, seatCount int
, equipment equipment[]
) INHERITS(venue);
Here is my ATTEMPT to INSERT:
这是我插入的尝试：
INSERT INTO lecture_room (building_code, floorNo, roomNo, length, width
, seatCount, equipment)
VALUES
('IT', 4, 2, 10, 15 ,120, ARRAY['projector','PAsystem','safe']),
But it gives me the following error:
但它给了我以下错误：
ERROR: column "equipment" is of type equipment[] but expression is of type text[] SQL state: 42804 Hint: You will need to rewrite or cast the expression.
ERROR: column "equipment" is of type equipment[] but expression is of type text[] SQL state: 42804 Hint: You will need to rewrite or cast the expression.
回答by Mark Stosberg
PostgreSQL doesn't know how to automatically cast input of type textto input of type equipment. You have to explicitly declare your strings as being of type equipment:
PostgreSQL 不知道如何将 typetext的输入自动转换为 type的输入equipment。您必须明确声明您的字符串类型为equipment：
ARRAY['projector','PAsystem','safe']::equipment[]
I confirmed this with SQL Fiddle.
我用SQL Fiddle确认了这一点。
回答by Erwin Brandstetter
The alternative to an ARRAY constructorlike @Mark correctly suppliedis to cast a string literal directly:
正确提供的像@Mark 这样的ARRAY 构造函数的替代方法是直接转换字符串文字：
'{projector,PAsystem,safe}'::equipment[]
This variant is shorter and some clients have problems with the ARRAY constructor, which is a function-like element.
此变体较短，并且一些客户端在 ARRAY 构造函数方面存在问题，这是一个类似函数的元素。
回答by harm
Old question, but a new answer. In modern versions of Postgres (tested with 9.6) none of this is required. It works as expected:
老问题，但一个新的答案。在现代版本的 Postgres（用 9.6 测试）中，这些都不是必需的。它按预期工作：
INSERT INTO lecture_room (equipment) VALUES ('{"projector", "safe"}');
回答by MJavaDev
Additionally to @harm answer, you can skip quotations marks:
除了@harm 答案，您还可以跳过引号：
INSERT INTO lecture_room (equipment) VALUES ('{projector, safe}');
