Getting list of values as array

Question

I have this query:

SELECT id FROM a

id is of type integer

I want to get the result as integer array: [26053,26021], this result is returned to a function which iterates on the elements.

Based on what I read in the manual I wrote this query:

select array_to_string( array( SELECT id FROM a where ... ), ',' )

The thing is that it returns me a string 26053,26021 and when I iterate on the result it does:

    ... (id =2 or id =6 or id =0 or id =5 or id =3 or id =,
         or id =2 or id =6 or id =0 or id =2 or id =1)

As you can see it treats the result as string not as array, each char is an element.

How do I get just an array? Is the only solution to convert the string back to array? Isn't there a way to convert it directly?

Erwin Brandstetter · Accepted Answer · 2015-08-10 09:17:23Z

2

For the simple case with a single result row, an array constructor is best (simplest, fastest). You actually have that already, just remove array_to_string(), which converts the array to a string (as the name implies):

SELECT ARRAY(SELECT id FROM a WHERE ... ) AS id_arr;

For cases with multiple result rows, look to the aggregate function array_agg(). Example:

SELECT b, array_agg(id) AS id_arr
FROM   a
WHERE  ...
GROUP  BY b;

Aside:
The standard Postgres syntax for an array constant (text representation of array) would be '{26053,26021}' - with curly braces, and single-quoted. Depending on the context, you may or may not have to add an explicit cast: '{26053,26021}'::int[] Or you could use another array constructor, this time based on integer constants instead of a subquery: ARRAY[26053,26021].

[26053,26021] is something in between that wouldn't work either way.

edited Aug 10, 2015 at 9:17

answered Aug 10, 2015 at 9:06

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

3 Comments

Erwin Brandstetter Over a year ago

Aside: Instead of "iterating on the elements", there is often a superior SQL-only solution.

John Over a year ago

That works. Is there a way to return the elements as (26053,26021) and not as {26053,26021} ? Later I use query like: SELECT * FROM .. WHERE id IN result where result is the array I got from this query. It won't work with { }

Erwin Brandstetter Over a year ago

@John: Maybe you should ask another question demonstrating your exact use case.

Collectives™ on Stack Overflow

Getting list of values as array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related