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?
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?
user_category with column id,user_id and category_id ?
I would use an EXISTS condition:
SELECT *
FROM categories c
WHERE EXISTS (Select *
from users u
where c.id = any(u.category_ids)
and u.id = 1);
EXISTS more faster then JOIN ?This should possibly be the fastest option, but you should EXPLAIN ANALYZE all the answers to make sure.
SELECT *
FROM categories c
WHERE id IN (SELECT unnest(category_ids) from users where id=1)
SELECT *
FROM categories c
JOIN (SELECT unnest(category_ids) AS id from users where id=1) AS foo
ON c.id=foo.id
The first query will remove duplicates in the array due to IN(), the second will not.
I didn't test the syntax so there could be typos. Basically unnest() is a function that expands an array to a set of rows, ie it turns an array into a table that you can then use to join to other tables, or put into an IN() clause, etc. The opposite is array_agg() which is an aggregate function that returns an array of aggregated elements. These two are quite convenient.
If the optimizer turns the =ANY() query into a seq scan with a filter, then unnest() should be much faster as it should unnest the array and use nested loop index scan on categories.
You could also try this:
SELECT *
FROM categories c
WHERE id =ANY(SELECT category_ids from users where id=1)
