Selecting from array of composite types in Postgres

user_7976964 0
Viewed 839

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?

postgresql sql
2 Answers

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
user_26504370

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'
user_79769640
Related