Menu

PostgreSQL string_agg and array_agg (GROUP_CONCAT)

string_agg joins the values of a group into one string and array_agg collects them into an array. Ordering, DISTINCT, FILTER, NULL handling and the errors people hit.

This page includes runnable editors. Edit, run, and see output instantly.

string_agg(expression, separator) joins the values of each group into one string, and array_agg(expression) collects them into an array. Put ORDER BY inside the call to control the order: string_agg(name, ', ' ORDER BY name).

IT returns Bo, Kim, Li and {6100,4800,3900}; Sales returns Ana, Raj and {5000,4200}. Both are ordinary aggregates like count or sum: they work per group with GROUP BY, and over the whole table without it.

Order the values

Without ORDER BY inside the aggregate, the values come in whatever order the rows arrive, which can change between runs. An ORDER BY at the end of the query sorts the result rows, not the values inside each string. The inner ORDER BY goes after the last argument, with no comma:

string_agg(name, ', ' ORDER BY hired_on DESC)
array_agg(name ORDER BY score DESC, name)

Remove duplicates with DISTINCT

Ada bought a pen twice, but DISTINCT lists it once: book, pen. With DISTINCT, the ORDER BY may only use the aggregated value. Ordering by another column fails with "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list".

How NULL and empty groups behave

string_agg skips NULL values and returns Bo, Li. array_agg keeps them: {Bo,Li,NULL}. To drop NULLs from array_agg, add FILTER (WHERE name IS NOT NULL).

Over zero rows both return NULL, not an empty string or empty array. Wrap them when the caller expects a value: COALESCE(string_agg(...), '') or COALESCE(array_agg(...), '{}').

Aggregate only some rows with FILTER

FILTER (WHERE ...) limits one aggregate to matching rows while the rest of the query sees all of them:

IT has 3 people and lists Bo, Li; Sales has 2 and lists Ana.

Attach a list to each row of another table

To show each customer with a list of their products, aggregate in a subquery, so the outer query needs no GROUP BY:

SELECT c.id, c.name, c.email,
       (SELECT string_agg(o.product, ', ' ORDER BY o.product)
        FROM orders o
        WHERE o.customer_id = c.id) AS products
FROM customers c;

Customers without orders stay in the result, with products as NULL. A join with GROUP BY keeps them only if it is a LEFT JOIN.

Common errors and variations

  • Numbers: string_agg takes text, so string_agg(id, ',') fails with "function string_agg(integer, unknown) does not exist". Cast it: string_agg(id::text, ',').
  • First N per group: slice the ordered array, (array_agg(name ORDER BY salary DESC))[1:2].
  • JSON output: json_agg and jsonb_agg collect rows into a JSON array, and jsonb_object_agg(key, value) builds an object; see JSONB.
  • Already have an array: array_to_string(arr, ', ') joins an existing array, and unnest turns it back into rows (arrays).
  • Window version: both work as window functions too, for example string_agg(name, ', ') OVER (PARTITION BY dept) to show the whole list on every row.

Frequently Asked Questions

What is the PostgreSQL equivalent of GROUP_CONCAT?

string_agg(column, ','). Unlike MySQL's GROUP_CONCAT, the separator is required, and the order goes inside the call: string_agg(name, ', ' ORDER BY name).

How do I use string_agg with numbers?

Cast the value to text: string_agg(id::text, ','). string_agg accepts only text (or bytea), so an integer argument fails with "function string_agg(integer, unknown) does not exist".

Does string_agg have a length limit?

No separate limit; the result is a normal text value, which can hold up to 1 GB. MySQL's GROUP_CONCAT truncates at group_concat_max_len, but PostgreSQL does not truncate.

How do I get the first N values with array_agg?

Order inside the aggregate and slice the result: (array_agg(name ORDER BY score DESC))[1:3] returns the top three names of each group.

Coddy programming languages illustration

Learn to code with Coddy

Get started