Search code examples
arrayspostgresqlsubquery

WHERE IN as Subquery in Postgres

I have two table. Table users has one column category_ids as array of integer integer[] which is storing as {1001,1002}

I am trying to get all categories details but it is not working.

SELECT * 
FROM categories 
WHERE id IN (Select category_ids from users where id=1)

When I run Select category_ids from users where id=1 I am getting result as {1001,1002}. How to make {1001,1002} as (1001,1002) or what should I change to make work above query?

Solution

  • You could use =ANY():

    SELECT  * 
FROM categories 
    JOIN users ON categories.id =any(category_ids)
WHERE   users.id = 1;

    But why are you using an array?