PostgreSQL is an amazing database, no surprises here. But every now and then I discover an amazing little feature that I was missing, this week was the time to discover string_agg.

Suppose that you have two tables course and student and want to list every student enrolled in a given discipline.

Usually I did this with a quick Python script by grouping the course and than using than making a simple ', '.join(). With the aggregate string_agg function I now do it directly in the query.

The string_agg is very similar to the join method in Python, from the PG docs:

Input values concatenated into a string, separated by delimiter

The function have the following signature: string_agg(expression, delimiter)

I’ve prepared a simple fiddle to show this, ignore the weird tables. :)

If you wish you can jump directly to it here.

Our course table:

| id |             name |
|----|------------------|
|  1 |      Mathematics |
|  2 |          English |
|  3 |       Portuguese |
|  4 | Computer Science |

And our student table, which have a FK to course:

| id |   name | course |
|----|--------|--------|
|  1 |    Joe |      1 |
|  2 |    Ana |      1 |
|  3 | Amanda |      4 |
|  4 |  Jacob |      3 |
|  5 |  Maria |      3 |
|  6 | Joanne |      1 |

Let’s run a simple query joining them together:

SELECT
    c.name AS "course",
    s.name AS "student"
FROM course c
    JOIN student s ON c.id = s.course
ORDER BY 1;

We now have this:

|           course | student |
|------------------|---------|
| Computer Science |  Amanda |
|      Mathematics |     Joe |
|      Mathematics |     Ana |
|      Mathematics |  Joanne |
|       Portuguese |   Jacob |
|       Portuguese |   Maria |

Now, editing this query to make it concatenate the students by course is very easy, changes are bold:

SELECT
    c.name AS "course",
    string_agg(s.name, ', ') AS "students"
FROM course c
    JOIN student s ON c.id = s.course
GROUP BY 1
ORDER BY 1;

And now this is our result:

|           course |         students |
|------------------|------------------|
| Computer Science |           Amanda |
|      Mathematics | Joe, Ana, Joanne |
|       Portuguese |     Jacob, Maria |

And if you are using Django the ORM supports it as well! Just take a look at the docs.

Cool, eh? :)