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 05 '13 at 06:26

score 3 · Accepted Answer · edited May 23 '17 at 12:03

3

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[])

edited May 23 '17 at 12:03

Community

answered Apr 05 '13 at 07:21

Sumither S

score 1 · Answer 2 · answered Apr 05 '13 at 06:30

1

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 05 '13 at 06:30

mvp

i need to take the count of the records, – Sumither S Apr 05 '13 at 06:34
this was not mentioned in your question. what's wrong with `SELECT count(*) ...`? – mvp Apr 05 '13 at 06:35

2 Answers2