CODEWITHSUNDEEP
arrayspostgresqlaggregate-functions
Lillian Milagros Carrasquillo
Lillian Milagros Carrasquillo

Reputation: 259

Alternatives to array_agg()?

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' ?

Upvotes: 1

Views: 9984

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

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.

Upvotes: 10

user330315
user330315

Reputation:

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()

Upvotes: 4

Kouber Saparev
Kouber Saparev

Reputation: 8105

Use the STRING_AGG function:

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

Upvotes: 2

Related Questions