How to create the array of rows by hand in PostgreSQL?

Question

I'm trying to create the array of rows with ::RECORD[] or ::RECORD by hand as shown below:

SELECT ARRAY['(John,Smith)','(David,Miller)']::RECORD[];

Or:

SELECT ARRAY['(John,Smith)'::RECORD,'(David,Miller)'::RECORD];

But I got the error below:

ERROR: input of anonymous composite types is not implemented

Actually, I could run the query without ::RECORD[] or ::RECORD below but the type is TEXT[]:

SELECT ARRAY['(John,Smith)','(David,Miller)'];

So, how can I create the array of rows by hand?

Zegarek · Accepted Answer · 2024-02-07 06:38:40Z

3

The answer is kind of already in your question: you could use row() constructors. Demo:

select array[row('a',1,true),row('b',4,false)];

array
{"(a,1,t)","(b,4,f)"}

select pg_typeof(array[row('a',1,true),row('b',4,false)]);

pg_typeof
record[]

answered Feb 7, 2024 at 6:31

Zegarek

29.9k5 gold badges27 silver badges32 bronze badges

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

1 Answer 1