Don’t need no stinkin’ GROUP_CONCAT

One of the things I loved about MySQL was GROUP CONCAT. A very, very useful function. I thought it was something I was going to have to learn to live without having shifted to PostgreSQL. But PostgreSQL has STRING_AGG() (string aggregate) which does much the same thing – who knew?! Others have also been surprised..

Here is an example:

SELECT country, string_agg(city, ‘, ‘ ORDER BY city DESC)
FROM cities
WHERE country = ‘New Zealand’
GROUP BY country;

>> “New Zealand”, “Whangarei, Whanganui, Wellington, Tauranga, Taupo, Rotorua, Palmerston North, New Plymouth, Nelson, Napier, Invercargill, Hamilton, Gisborne, Dunedin, Christchurch, Auckland”

Note the odd location of ORDER BY, and the absence of a comma between the ORDER BY and the previous argument! See Aggregate Expressions