Postgres - CRUD operations with arrays of composite types

Question

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?

Laurenz Albe · Accepted Answer · 2020-01-20 09:35:32Z

1

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.

edited Jan 20, 2020 at 9:35

answered Jan 20, 2020 at 7:31

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DroidOS Over a year ago

Thank you. You imply that using composite types with WHERE conditions is not a good idea. Could you elaborate a bit on that? What sort of complications/limitations does it provoke?

Laurenz Albe Over a year ago

Thanks for the correction! Just try to come up with a SELECT that filters the results based on one attribute of any array element in a dow_id[]. You'll find that such a query is difficult to write, and you could not speed it up with an index.

Collectives™ on Stack Overflow

Postgres - CRUD operations with arrays of composite types

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related