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
– Sumither S, Commented Apr 5, 2013 at 6:26

Community · Accepted Answer · 2017-05-23 12:03:20Z

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

CommunityBot

11 silver badge

answered Apr 5, 2013 at 7:21

Sumither S

1292 silver badges10 bronze badges

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

Comments

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

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 5, 2013 at 6:30

mvp

118k15 gold badges132 silver badges155 bronze badges

i need to take the count of the records,

this was not mentioned in your question. what's wrong with SELECT count(*) ...?