Postgresql Select from values in array

Question

i am converting multiple rows in to a array using array_agg() function, and i need to give that array to a select statements where condition.

My query is,

SELECT * FROM table WHERE id = 
  ALL(SELECT array_agg(id) FROM table WHERE some_condition)

but it gives error, how can i over come it..

ERROR:operator does not exist: bigint = bigint[] HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. — Sumither S, Apr 5 '13 at 6:26

Sumither S · Accepted Answer · 2013-05-13 06:45:17Z

up vote 1 down vote accepted

the error has been cleared by type casting the array, using my query like this

 SELECT * FROM table WHERE id = 
    ALL((SELECT array_agg(id) FROM table WHERE some_condition)::bigint[])

answered Apr 5 '13 at 7:21

Sumither S
398

mvp · Answer 2 · 2013-04-05 06:30:42Z

up vote 0 down vote

It seems like you are over-complicating things. As far as I can tell, your query should be equivalent to simple:

SELECT * FROM table WHERE some_condition

Or, if you are selecting from 2 different tables, use join:

SELECT table1.*
FROM table1
JOIN table2 ON table1.id = table2.id
WHERE some_condition

Not only this is simpler, it is also faster than fiddling with arrays.

answered Apr 5 '13 at 6:30

mvp
24k41746

i need to take the count of the records, – Sumither S Apr 5 '13 at 6:34

this was not mentioned in your question. what's wrong with SELECT count(*) ...? – mvp Apr 5 '13 at 6:35

2 Answers