Skip to content
Advertisement

How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?

I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:

and I wanted to group by company_id to get something like:

There is a built-in function in mySQL to do this group_concat

Advertisement

Answer

PostgreSQL 9.0 or later:

Modern Postgres (since 2010) has the string_agg(expression, delimiter) function which will do exactly what the asker was looking for:

Postgres 9 also added the ability to specify an ORDER BY clause in any aggregate expression; otherwise you have to order all your results or deal with an undefined order. So you can now write:

PostgreSQL 8.4.x:

PostgreSQL 8.4 (in 2009) introduced the aggregate function array_agg(expression) which collects the values in an array. Then array_to_string() can be used to give the desired result:

PostgreSQL 8.3.x and older:

When this question was originally posed, there was no built-in aggregate function to concatenate strings. The simplest custom implementation (suggested by Vajda Gabo in this mailing list post, among many others) is to use the built-in textcat function (which lies behind the || operator):

Here is the CREATE AGGREGATE documentation.

This simply glues all the strings together, with no separator. In order to get a “, ” inserted in between them without having it at the end, you might want to make your own concatenation function and substitute it for the “textcat” above. Here is one I put together and tested on 8.3.12:

This version will output a comma even if the value in the row is null or empty, so you get output like this:

If you would prefer to remove extra commas to output this:

Then add an ELSIF check to the function like this:

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement