PostgreSQL string_agg Function

  1. Use the string_agg() to Concatenate Strings in PostgreSQL
  2. Replace the Duplicate From string_agg() in PostgreSQL
  3. Order in string_agg() in PostgreSQL

This article discusses using the string_agg() function to concatenate strings of a string field in a PostgreSQL GROUP BY query.

Use the string_agg() to Concatenate Strings in PostgreSQL

string_agg() combine the strings that have no null characters. It takes the column name and delimiter (i.e., comma, dash, letters) as an input.

We have the following table of the employees and associate company. We’re asked to list all the employee names separated by a comma for each company.

Here’s the following data we have.

postgres=# SELECT * from employee;
 id | companyname | employeename | salary
----+-------------+--------------+---------
  1 | Sony        | Bob          | 1500.55
  2 | Sony        | Bob          | 1500.55
  3 | Sony        | Jhon         |    2000
  4 | Sony        | Dow          |   15000
  5 | Sony        | Alice        |    5602
  6 | Google      | Jade         | 1500.55
  7 | Google      | Penny        | 1500.55
  8 | Google      | Kat          | 1500.55
  9 | Google      | Merly        | 1500.55
 10 | Hitachi     | Raymond      | 1500.55
 11 | Hitachi     | Skye         | 1500.55
 12 | Hitachi     | Sova         | 1500.55
 13 | Hitachi     | Jenny        | 1500.55

Let’s try to use the string_agg, and it will be grouped by Company Name. Now, notice that there’s an entry twice.

Use string_agg() in PSQL

SELECT companyname As CompanyName, STRING_AGG(employeename,', ') as Employees
FROM employee
GROUP BY companyname;

Output:

 companyname |         employees
-------------+----------------------------
 Sony        | Bob, Bob, Jhon, Dow, Alice
 Google      | Jade, Penny, Kat, Merly
 Hitachi     | Raymond, Skye, Sova, Jenny
(3 rows)

Now, we can see that it prints all the employee names for each company. We can see that Bob is printed twice in the Sony if we notice.

Replace the Duplicate From string_agg() in PostgreSQL

To remove the duplicate, we can add the DISTINCT keyword in the string_agg() function before the column name that we want to concat.

So, the modification of the above SQL that will remove the duplicate from the string_agg() will look like the following.

SELECT companyname As CompanyName, STRING_AGG(employeename,', ') as Employees
FROM employee
GROUP BY companyname;

Output:

 companyname |         employees
-------------+----------------------------
 Google      | Jade, Kat, Merly, Penny
 Hitachi     | Jenny, Raymond, Skye, Sovav
 Sony        | Alice, Bob, Dow, Jhon
(3 rows)

Order in string_agg() in PostgreSQL

string_agg() function can take parameters as a SQL query. So, for example, if you want to concat the string in reverse order than simply just writing the order, you can change the order of the output string.

So, the updated code will be like the following.

SELECT companyname As CompanyName, STRING_AGG(DISTINCT employeename,', ' ORDER BY employeename desc) as Employees
FROM employee
GROUP BY companyname;

Output:

 companyname |         employees
-------------+----------------------------
 Google      | Penny, Merly, Kat, Jade
 Hitachi     | Sova, Skye, Raymond, Jenny
 Sony        | Jhon, Dow, Bob, Alice
(3 rows)

Here’s a blog for the string_agg. Also, you can look over the official documentation.

You can find the create table and insert commands from this pastebin link.

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - PostgreSQL String

  • Escape a Single Quote in PostgreSQL