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?

Marth · Accepted Answer · 2020-08-06 10:01:49Z

6

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 6, 2020 at 10:01

Marth

24.9k3 gold badges68 silver badges76 bronze badges

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

Comments

prenone · Accepted Answer · 2020-08-06 09:42:56Z

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 6, 2020 at 9:42

prenone

3344 silver badges20 bronze badges

1 Comment

Marth Over a year ago

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 stackoverflow.com/a/23004157/2650437).

Collectives™ on Stack Overflow

Selecting from array of composite types in Postgres

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related