Selecting from array of composite types in Postgres

Question

I have a composite type pin:

CREATE TYPE public.pin AS
(
    id uuid,
    name text,
);

And a table device:

CREATE TABLE public.devices
(
    name text COLLATE pg_catalog."default" NOT NULL,
    pins pin[] NOT NULL
)

How do I write a query to select the pin.name from the devices table where pin.id is equal to a known id?

score 4 · Accepted Answer · answered Aug 06 '20 at 10:01

4

You can use a lateral join for this:

SELECT pin.name
FROM devices, unnest(pins) AS pin -- implicit lateral join
WHERE pin.id = '77068690-787c-431d-9a6f-bd2a069fa5a4' -- random uuid

answered Aug 06 '20 at 10:01

Marth

score 1 · Answer 2 · answered Aug 06 '20 at 09:42

1

I managed to solve the problem with this query:

SELECT name FROM (SELECT (UNNEST(pins)::pin).id, (UNNEST(pins)::pin).name FROM public.devices) AS _pins WHERE _pins.id = 'uuid'

answered Aug 06 '20 at 09:42

prenone

1

I would recommend moving the `UNNEST(pins)` in the `FROM` clause (using a `JOIN LATERAL`), afaik using set-returning functions in the `SELECT` clause is not well defined (see https://stackoverflow.com/a/23004157/2650437). – Marth Aug 06 '20 at 09:53

2 Answers2