Postgres-使用复合类型数组的CRUD操作 [英] Postgres - CRUD operations with arrays of composite types
问题描述
我刚刚发现的Postgres的一个真正整洁的功能是能够定义复合类型的功能-在他们的文档中也称为 ROWS 和 RECORDS 。请考虑以下示例
创建类型dow_id AS
（
tslot smallint，
day smallint
）;
现在考虑以下表格
CREATE SEQUENCE test_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1;
创建表test_simple_array
（
id整数默认DEFAULT nextval（‘test_id_seq’）非空，
dx整数[]
）;
创建表test_composite_simple
（
id整数默认值nextval（‘test_id_seq’）非空，
dx dow_id
）;
创建表test_composite_array
（
id整数默认值nextval（’test_id_seq’）非空，
dx dow_id []
）;
前两个表的CRUD操作相对简单。例如，
INSERT INTO test_simple_array（dx）VALUES（'{1,1}'）;
插入到test_composite_simple（dx）值（ROW（1,1））;
但是，我不能弄清楚当表中有记录/复合类型数组，如 test_composite_array 所示。我已经尝试过
插入到test_composite_array（dx）值中（ARRAY（ROW（1,1），ROW（1,2） ））;
由于消息失败
错误： ROW处或附近的语法错误
和
插入到test_composite_array（dx）VALUES（ {（1,1），（1,2）}））；
由于消息失败
错误：列 {{1,1），（1,2）}不存在
和
插入到test_composite_array（dx）VALUES（'{（1,1），（1,2） }'）;
这似乎有效，尽管自从随后的
起我感到困惑
从test_composite_array SELECT dx
返回似乎是字符串结果的 {（1,1），（1 ，2）} ，但进一步查询例如
SELECT id FROM test_composite_array WHERE（dx [1 ]）。tslot = 1;
有效，我也尝试了以下
SELECT（dx [1]）。day FROM test_composite_array;
UPDATE test_composite_array SET dx [1] .day = 99 WHERE（dx [1] ）.tslot = 1;
SELECT（dx [1]）。day FROM test_composite_array;
这可同时使用
更新test_composite_array SET（dx [1]）。day = 99 WHERE（dx [1]）。tslot = 1;
失败，我发现我正在弄清楚如何处理记录/复合类型数组在Postgres中，经过反复试验，并且-整个Postgres文档是g从总体上讲非常好-文档中似乎没有对该主题的清晰讨论。任何人都可以向我提出关于如何在Postgres中操作复合类型数组的权威性讨论。
此外，当发生任何意外情况时，
您需要带有 ARRAY 的方括号：
ARRAY [ROW（1,1）:: dow_id，ROW（1,2）:: dow_id]
警告：复合类型是一个很棒的功能，但是如果过度使用它们，将会使您的生活更加艰难。您想在 WHERE 或 JOIN 条件中使用复合类型的元素时，您在做错事，你会受苦的标准化关系数据有充分的理由。
One really neat feature of Postgres that I have only just discovered is the ability to define composite type - also referred to in their docs as ROWS and as RECORDS. Consider the following example
CREATE TYPE dow_id AS
(
tslot smallint,
day smallint
);
Now consider the following tables
CREATE SEQUENCE test_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1;
CREATE TABLE test_simple_array
(
id integer DEFAULT nextval('test_id_seq') NOT NULL,
dx integer []
);
CREATE TABLE test_composite_simple
(
id integer DEFAULT nextval('test_id_seq') NOT NULL,
dx dow_id
);
CREATE TABLE test_composite_array
(
id integer DEFAULT nextval('test_id_seq') NOT NULL,
dx dow_id[]
);
CRUD operations on the first two tables are relatively straightforward. For example
INSERT INTO test_simple_array (dx) VALUES ('{1,1}');
INSERT INTO test_composite_simple (dx) VALUES (ROW(1,1));
However, I have not been able to figure out how to perform CRUD ops when the table has an array of records/composite types as in test_composite_array. I have tried
INSERT INTO test_composite_array (dx) VALUES(ARRAY(ROW(1,1),ROW(1,2)));
which fails with the message
ERROR: syntax error at or near "ROW"
and
INSERT INTO test_composite_array (dx) VALUES("{(1,1),(1,2)}");
which fails with the message
ERROR: column "{(1,1),(1,2)}" does not exist
and
INSERT INTO test_composite_array (dx) VALUES('{"(1,1)","(1,2)"}');
which appears to work though it leaves me feeling confused since a subsequent
SELECT dx FROM test_composite_array
returns what appears to be a string result {"(1,1),(1,2)} although a further query such as
SELECT id FROM test_composite_array WHERE (dx[1]).tslot = 1;
works. I also tried the following
SELECT (dx[1]).day FROM test_composite_array;
UPDATE test_composite_array SET dx[1].day = 99 WHERE (dx[1]).tslot = 1;
SELECT (dx[1]).day FROM test_composite_array;
which works while
UPDATE test_composite_array SET (dx[1]).day = 99 WHERE (dx[1]).tslot = 1;
fails. I find that I am figuring out how to manipulate arrays of records/composite types in Postgres by trial and error and - altough Postgres documentation is generally excellent - there appears to be no clear discussion of this topic in the documentation. I'd be much obliged to anyone who can point me to an authoritative discussion of how to manipulate arrays of composite types in Postgres.
That apart are there any unexpected gotchas when working with such arrays?
You need square brackets with ARRAY:
ARRAY[ROW(1,1)::dow_id,ROW(1,2)::dow_id]
A warning: composite types are a great feature, but you will make your life harder if you overuse them. As soon as you want to use elements of a composite type in WHERE or JOIN conditions, you are doing something wrong, and you are going to suffer. There are good reasons for normalizing relational data.
这篇关于Postgres-使用复合类型数组的CRUD操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
