How to make use of a postgres Array in where query?

Question

One of the table in postgreSQL has a column of datatype text[]

TableA:
id              uuid
tableb_ids      text[]

TableB:
id              uuid
name            text

Now I need to write a query like:

select * from tableB where id in (select tableb_ids from tableA where id ="xxxx-xxxx-xxxx-xxxx")

I cannot change the schema/table definitions. i.e) I cannot keep many entries for every entry of tableB in tableA. TableA is a complex table.

Something like SELECT b.* FROM tableb b JOIN tablea a ON b.id = ANY(a.tableb_ids) — 404
– 404, Commented Nov 11, 2019 at 11:22
@404 I get ERROR: operator does not exist: uuid = text[] when I try that way — Surya
– Surya, Commented Nov 11, 2019 at 11:40
@Surya: please do not extend your question once you have an answer to your initial problem. Ask a new question instead. — user330315
– user330315, Commented Nov 11, 2019 at 12:15
@404: you should add that as an answer, so it can be accepted and the question can be marked as resolved — user330315
– user330315, Commented Nov 11, 2019 at 12:17

404 · Accepted Answer · 2019-11-11 12:34:34Z

1

To find records in one table whose record ids are contained in an array in some other table, you can join on the tables:

SELECT b.*
FROM tableb b
INNER JOIN tablea a
    ON b.id::TEXT = ANY(a.tableb_ids)
    AND a.id = 'xxxx-xxxx-xxxx-xxxx'

Another way you could do it:

SELECT b.*
FROM tableb b
WHERE id IN (
    SELECT UNNEST(a.tableb_ids)
    FROM tablea a
    WHERE a.id = 'xxxx-xxxx-xxxx-xxxx'
) x
-- not sure if the aliases are needed in the subquery

answered Nov 11, 2019 at 12:34

404

8,7522 gold badges33 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

How to make use of a postgres Array in where query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related