CODEWITHSUNDEEP
arrayspostgresqlpostgresql-9.1
Sumither S
Sumither S

Reputation: 129

Postgresql Select from values in array

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..

Upvotes: 3

Views: 6738

Answers (2)

Sumither S
Sumither S

Reputation: 129

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

reference link

Upvotes: 3

mvp
mvp

Reputation: 116058

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.

Upvotes: 1

Related Questions