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?

MatheusOl · Accepted Answer · 2013-02-25 19:15:20Z

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)

answered Feb 25, 2013 at 19:15

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

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

Comments

Clodoaldo Neto · Accepted Answer · 2013-02-25 11:36:28Z

0

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

answered Feb 25, 2013 at 11:36

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges