SELECTing data using a Postgres Array

Question

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?

Answer 1

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)

Answer 2

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