Some RDBMSs like MySQL and MariaDB have a GROUP_CONCAT() function that allows you to return a query column as a delimited list (for example, a comma separated list).

PostgreSQL has a similar function called STRING_AGG(). This function works in pretty much the same way that GROUP_CONCAT() works in MySQL and MariaDB.

Example

Here’s a basic example to demonstrate how PostgreSQL’s STRING_AGG() function works:

SELECT STRING_AGG(genre, ',') FROM Genres;

Result:

Rock,Jazz,Country,Pop,Blues,Hip Hop,Rap,Punk

Here’s what happens when we just do the query without the STRING_AGG() function:

SELECT genre FROM Genres;

Result:

+---------+
|  genre  |
+---------+
| Rock    |
| Jazz    |
| Country |
| Pop     |
| Blues   |
| Hip Hop |
| Rap     |
| Punk    |
+---------+
(8 rows)

We get eight rows, each with a separate value, instead of one long comma separated row of all values.

If anything, PostgreSQL’s STRING_AGG() is more like MySQL’s GROUP_CONCAT() than MariaDB’s function of the same name. I say this because MariaDB’s GROUP_CONCAT() allows us to provide a LIMIT clause (as from MariaDB 10.3.3), right from within the function itself. MySQL GROUP_CONCAT() doesn’t support the LIMIT clause, and neither does PostgreSQL’s STRING_AGG() function (at least, not at the time of this writing).

Another difference is that PostgreSQL’s STRING_AGG() requires a second argument (that specifies the delimiter to use). Both MySQL and MariaDB make this optional with their GROUP_CONCAT() functions.

Postgres’s STRING_AGG() accepts an ORDER BY clause, and a DISTINCT clause (as do MariaDB and MySQL’s GROUP_CONCAT() functions).

See STRING_AGG() Function in PostgreSQL for more examples.

And in case you’re interested, SQL Server also has a STRING_AGG() function that works in pretty much the same way.