UNNEST a composite array into rows and columns in Postgres

Question

Postgres 11.7.

I'm trying to unpack an array where each item has multiple elements, and can't seem to get the syntax right. I'm hoping that someone can point out what I'm missing. Here's an example:

select
unnest(array[

                 ('Red Large Special',     1),
                 ('Blue Small',            5),
                 ('Green Medium Special', 87)

              ]) as item_list

This is what I want:

item_name               item_id  
Red Large Special       1
Blue Small              5
Green Medium Special   87

This is what I get:

base_strings
("Red Large Special",1)
("Blue Small",5)
("Green Medium Special",87)

I believe that I need a column specification list, something like this:

select * from
unnest(array[

                 ('Red Large Special',    1),
                 ('Blue Small',        5),
                 ('Green Medium Special', 87)

              ]) AS item_list(item_name citext, item_id int4)

What I get is:

ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned type unknown at ordinal position 1, but query expects citext. (Line 9)

I can get it to work if I formally declare a custom, composite type:

CREATE TYPE item_details AS (
   item_name citext,
   item_id   int4);

select * from
unnest(array[

                 ('Red Large Special',    1),
                 ('Blue Small',        5),
                 ('Green Medium Special', 87)

              ]::item_details[]) as item_list

This is right:

item_name             item_id
Red Large Special     1
Blue Small            5
Green Medium Special  87

Is there any way to get the same result without declaring a type? I'm looking for a solution where I can define the type on-the-fly. I'm pretty sure that I've done this in Postgres in the past, but maybe it was with JSONB?

I've consulted the Fine Documentation on table returning expressions, but couldn't follow it. There's not really an example there, I I'm unable to extrapolate from the grammar summary.

https://www.postgresql.org/docs/current/queries-table-expressions.html

Follow-up

Two great answers that stop me from chasing my own tail. In this case, the task is to open up some functionality to multiple clients, so I'm probably better off using JSON than the Postgres-specific array syntax. @a_horse_with_no_name leads me to this kind of code, starting from JSON text:

with expanded_data AS (
 select * 
   from json_to_recordset(
        '[
             {"base_text":"Red Large Special","base_id":1},
             {"base_text":"Blue Small","base_id":5},
             {"base_text":"Green Medium Special","base_id":87}
         ]')
      AS unpacked (base_text citext, base_id citext)
 )

select base_text,
       base_id

  from expanded_data

Abelisto · Accepted Answer · 2020-05-08 12:36:01Z

6

Because string constants actually have unknown type you need to specify the desired type explicitly:

# select * from
unnest(array[
                 ('Red Large Special'::citext,    1),
                 ('Blue Small'::citext,        5),
                 ('Green Medium Special'::citext, 87)
              ]) AS item_list(item_name citext, item_id int4);
┌──────────────────────┬─────────┐
│      item_name       │ item_id │
├──────────────────────┼─────────┤
│ Red Large Special    │       1 │
│ Blue Small           │       5 │
│ Green Medium Special │      87 │
└──────────────────────┴─────────┘

answered May 8, 2020 at 12:36

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

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

2 Comments

user330315 Over a year ago

"Because string constants actually have unknown type" - this is one of the things I don't really like (and don't understand) about Postgres :(

Abelisto Over a year ago

@a_horse_with_no_name Maybe advantages more then disadvantages of this feature.

user330315 · Accepted Answer · 2020-05-08 12:21:16Z

4

One way I can think of, is to convert it into a jsonb array:

select item ->> 'f1' as item_name, 
       (item ->> 'f2')::int as item_id 
from jsonb_array_elements(to_jsonb(array[
                 ('Red Large Special',     1),
                 ('Blue Small',            5),
                 ('Green Medium Special', 87)
              ])) t(item)

answered May 8, 2020 at 12:21

user330315

Comments

Victor · Accepted Answer · 2022-07-15 19:02:26Z

2

I had has the some problem and ended here looking a solution, i read these useful answer and finally I guess we have it :

select * from
unnest(array[
    ('Red Large Special'::text,     1),
    ('Blue Small'::text,            5),
    ('Green Medium Special'::text, 87)
]) as item_list(name text, id int)

No json required and also no need to declare a type ;) Morris de Oryx you were sooo close!

DO MIND that explicit casting to ::text is mandatory, if not you will experience some trouble as text without ::text will be resolved as unknow by postgres's engine.

answered Jul 15, 2022 at 19:02

Victor

3,9983 gold badges43 silver badges66 bronze badges

4 Comments

alphabetasoup Over a year ago

So you do need to declare a type?

Victor Over a year ago

@alphabetasoup using my solution you don't need to declare and define a new type. Just take in mind the casting.

alphabetasoup Over a year ago

That's precisely my point :)

Florat Over a year ago

@alphabetasoup no type polluting the database?

Collectives™ on Stack Overflow

UNNEST a composite array into rows and columns in Postgres

Follow-up

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Follow-up

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related