Alternatives to array_agg()?

Question

Is there any alternative to the PostgreSQL array_agg() function so that it doesn't return values in the format: '{x,y,z,}'.
Can I have it return just: 'x,y,z' ?

Erwin Brandstetter · Accepted Answer · 2019-05-01 13:58:24Z

10

In PostgreSQL 9.0 or later use string_agg(val, ',').
It returns a string with delimiters of your choosing.

array_agg(val) returns an array, no surprise there. The curly braces you see are integral part of array literals - the text representation of arrays.

In older versions (or any version really) you can substitute with array_to_string(array_agg(val), ',').

Or, quick'n'dirty: trim(array_agg(val)::text, '{}' - if values never start or end with curly braces.

edited May 1, 2019 at 13:58

answered Mar 1, 2012 at 22:19

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

Comments

score 4 · Accepted Answer · 2012-10-25 06:11:15Z

4

If you are not on 9.0 yet (which has the already mentioned string_agg() function) you can use array_to_string() on the result of array_agg()

edited Oct 25, 2012 at 6:11

answered Mar 1, 2012 at 22:21

user330315

Comments

Dave Jarvis · Accepted Answer · 2012-10-25 00:35:34Z

2

Use the STRING_AGG function:

SELECT
  STRING_AGG(name, ',')
FROM
  person;

edited Oct 25, 2012 at 0:35

Dave Jarvis

31.3k43 gold badges186 silver badges326 bronze badges

answered Mar 1, 2012 at 22:19

Kouber Saparev

8,2552 gold badges32 silver badges28 bronze badges

Collectives™ on Stack Overflow

Alternatives to array_agg()?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related