PostgreSQL string_agg Function
-
Use the
string_agg()to Concatenate Strings in PostgreSQL -
Replace the Duplicate From
string_agg()in PostgreSQL -
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.
