Aggregation

What replaces GROUP_CONCAT in PostgreSQL?

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

PostgreSQL uses string_agg, and the delimiter is a required argument: string_agg(city, ', ') where MySQL would take GROUP_CONCAT(city).

Leave the delimiter off and the call fails by naming the type you handed it. On a text column that reads function string_agg(text) does not exist, which sounds like the function is missing and is not what happened. Both errors are below, with the working call after each one.

The examples read customers, one row per customer, with a country and a city that is null for some of them, and the exercises at the end also join orders. Both table definitions are below, and every number on this page comes out of a query you can see.

You can edit and run two of the queries below in your browser, and there are two exercises at the end. See our other guides to aggregating query output if you came here with a different aggregation problem.

Schema · ecommerce2 tables? = nullable

customers

idinteger

nametext

emailtext

city?text

countrytext

created_attimestamptz

is_activeboolean

orders

idinteger

customer_id?integer

ordered_attimestamptz

statustext

total_amountnumeric

SELECT count(*) AS customer_rows,
       count(city) AS rows_with_a_city,
       count(DISTINCT country) AS countries
FROM customers;

customer_rows	rows_with_a_city	countries
70	61	22

the customers table in our sandbox: 70 rows, 9 of them with no city

What happens if you run MySQL’s GROUP_CONCAT in PostgreSQL?

The call fails, and PostgreSQL names the argument type it could not match. Here is the MySQL habit, against a table of customers:

SELECT country, GROUP_CONCAT(name)
FROM customers
GROUP BY country;

PostgreSQL responds function group_concat(text) does not exist

PostgreSQL ships no function called group_concat, so there is nothing to match the call against. MySQL documents GROUP_CONCAT as taking an optional SEPARATOR that defaults to a comma.

Run it yourself, then try the replacement in the next section:

SELECT country, GROUP_CONCAT(name)
FROM customers
GROUP BY country;

Why does STRING_AGG say it does not exist?

Because the call has one argument and every string_agg overload takes two. PostgreSQL reports a failed match by naming the argument types it was handed, so a missing second argument reads as a missing function:

SELECT country, STRING_AGG(name)
FROM customers
GROUP BY country;

PostgreSQL responds function string_agg(text) does not exist

Ask the catalog and there are exactly two: string_agg(text, text) returning text, and string_agg(bytea, bytea) returning bytea. Neither has a one-argument form, and PostgreSQL applies no default separator. Add the delimiter and the call resolves. Here it is against two country groups, small enough to print whole:

SELECT country, string_agg(city, ', ') AS cities
FROM customers
WHERE country IN ('DE', 'GB')
GROUP BY country
ORDER BY country;

country	cities
DE	Berlin, Munich, Berlin
GB	London, Manchester, Birmingham, London, Edinburgh, London, London

two country groups from our customers table, with no sort inside the aggregate

How do you sort the values inside the list?

Put ORDER BY inside the parentheses, after the delimiter, with no comma in front of it. The same three German cities come back in a different order:

SELECT country, string_agg(city, ', ' ORDER BY city) AS cities
FROM customers
WHERE country IN ('DE', 'GB')
GROUP BY country
ORDER BY country;

country	cities
DE	Berlin, Berlin, Munich
GB	Birmingham, Edinburgh, London, London, London, London, Manchester

the same rows as above, ordered inside the aggregate

That ORDER BY sorts the values being joined together. The ORDER BY at the end of the query sorts the rows the query returns, which is a different job, and putting the inner one there instead raises an error about GROUP BY. Sorting inside an aggregate has a few more rules worth knowing, including what DISTINCT does to it.

Try it: change ORDER BY city to ORDER BY city DESC and run it again. Only the order inside each string changes.

SELECT country, string_agg(city, ', ' ORDER BY city) AS cities
FROM customers
WHERE country IN ('DE', 'GB')
GROUP BY country
ORDER BY country;

How do you remove duplicates from the list?

Write DISTINCT immediately after the opening parenthesis. London appears four times among the British customers and once in the result:

SELECT country, string_agg(DISTINCT city, ', ' ORDER BY city) AS cities
FROM customers
WHERE country IN ('DE', 'GB')
GROUP BY country
ORDER BY country;

country	cities
DE	Berlin, Munich
GB	Birmingham, Edinburgh, London, Manchester

the same two groups with duplicate cities collapsed

What happens to rows where the value is null?

string_agg leaves them out, and the result says nothing about it. One British customer in our sandbox has no city. Put the counts next to the list and the gap is visible:

SELECT country,
       count(*) AS rows_in_group,
       count(city) AS rows_with_a_city,
       string_agg(city, ', ' ORDER BY city) AS listed
FROM customers
WHERE country = 'GB'
GROUP BY country;

country	rows_in_group	rows_with_a_city	listed
GB	8	7	Birmingham, Edinburgh, London, London, London, London, Manchester

eight rows in the group, seven with a city, seven cities in the string

Eight rows went in and seven cities came out. Nobody reading the string alone can tell. This is worth knowing before you hand a list like that to somebody as a report, and it is not true of every aggregate: the JSON and array collectors keep their nulls.

When should you build an array or JSON instead of a string?

When something other than a person is going to read it. A string has to be split again at the other end, and it cannot say which value was null. All three collectors take the same inner ORDER BY, and only string_agg takes a delimiter:

SELECT string_agg(city, ', ' ORDER BY city) AS as_text,
       array_agg(city ORDER BY city)::text AS as_array,
       jsonb_agg(city ORDER BY city)::text AS as_json
FROM customers
WHERE country = 'DE';

as_text	as_array	as_json
Berlin, Berlin, Munich	{Berlin,Berlin,Munich}	["Berlin", "Berlin", "Munich"]

the same three German cities as text, as a Postgres array and as a JSON array

Use string_agg for something a human reads, array_agg when the rest of the query needs the values separately, and jsonb_agg when an application is going to parse the result. Returning JSON from a query covers the last one, including how to nest a list inside an object.

Practice: collecting rows into one string

string_agg is node N050 of our free SQL course, which covers the function itself and what it does inside a GROUP BY. No account, nothing to install, nothing to pay.