How to aggragate integers in postgresql?

Question

I have a query that gives a list of identifiers:

ID 2 3 4 5 6 25 

ID integer .

I want to get this result in ARRAY integers :

 ID 2,3,4,5,6,25 

I wrote this query:

 select string_agg(ID::text,',') from A where ..... 

I need to convert it to text, otherwise it will not work. string_agg expect to receive (text,text)

this works great, as this result should subsequently be used in many places that expect ARRAY integers.

I tried:

 select ('{' || string_agg(ID::text,',') || '}')::integer[] from A WHERE ... 

which gives: {2,3,4,5,6,25} in type int4 integer[] but this is not the right type ... I need the same type as ARRAY .

eg SELECT ARRAY[4,5] gives an array integer[]

in simple words I want the result of my query to work (for example):

 select * from b where b.ID = ANY (FIRST QUERY RESULT) // aka: = ANY (ARRAY[2,3,4,5,6,25]) 

this does not work, since ANY expects an array and it does not work with a regular integer [], I get an error message:

ERROR: operator does not exist: integer = integer []

Note: the query result is part of the function and will be stored in a variable for later work. Do not send it to places where you bypass the problem, and suggest a solution that ARRAY integers will not give.

EDIT : why

 select * from b where b.ID = ANY (array [4,5]) 

works. but

 select * from b where b.ID = ANY(select array_agg(ID) from A where ..... ) 

does not work

 select * from b where b.ID = ANY(select array_agg(4)) 

not working either

the error is still:

ERROR: operator does not exist: integer = integer []

Answer 1

The expression select array_agg(4) returns a rowset (actually a rowset with 1 row). Therefore, the request

 select * from b where b.id = any (select array_agg(4)) -- ERROR 

tries to compare an integer (b.id) with the value of a row (which has 1 integer [] column). This causes an error.

To fix this, you should use a subquery that returns integers (and not arrays of integers):

 select * from b where b.id = any (select unnest(array_agg(4))) 

Alternatively, you can put the column name of the select array_agg(4) result as any argument, for example:

 select * from b cross join (select array_agg(4)) agg(arr) where b.id = any (arr) 

or

 with agg as ( select array_agg(4) as arr) select * from b cross join agg where b.id = any (arr) 

More formally, the first two queries use any form:

 expression operator ANY (subquery) 

and the other two use

 expression operator ANY (array expression) 

as described in the documentation: 9.22.4. ANY / SOME and 9.23.3. ANY / SOME (array) .

Answer 2

How about this request? Does this mean the expected result?

 SELECT * FROM b b_out WHERE EXISTS (SELECT 1 FROM b b_in WHERE b_out.id = b_in.id AND b_in.id IN (SELECT <<first query that returns 2,3,4,...>>)) 

What I was trying to do was split the ANY logic into two separate logical checks to achieve the same result.

Therefore, ANY equivalent to the EXISTS combination of at least one of the IN values ​​of your list of values ​​returned by the first SELECT .