CODEWITHSUNDEEP
arrayspostgresql
Neil Middleton
Neil Middleton

Reputation: 22238

SELECTing data using a Postgres Array

I have a table that contains a column user_ids which is a Postgres Array.

I need to select all messages from another table where the column user_id is one of the ids in the given array.

In Psuedo-sql:

select users.*
from users
where id IN a_postgres_array

Any ideas?

Upvotes: 0

Views: 168

Answers (2)

MatheusOl
MatheusOl

Reputation: 11815

You could use the ANY operator. From your sample:

select users.*
from users
where id =ANY(a_postgres_array)

When using two tables, it could be a JOIN, something like:

SELECT users.*
FROM users INNER JOIN table_with_array ON users.id =ANY(table_with_array.a_postgres_array)

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

select users.*
from users
where id IN (
    select unnest(a_postgres_array)
    from t
    where columnX = some_value
    )

Upvotes: 0

Related Questions