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.
