Often in PostgreSQL you may want to use the string_agg function to concatenate strings together, returning only distinct strings.
You can use the following syntax to do so:
SELECT team, string_agg(DISTINCT (name), ', ') AS player_names
FROM athletes
GROUP BY team;
This particular example will concatenate only the distinct strings from the name column together into a new column named player_names using a comma as a separator.
The following example shows how to use this syntax in practice.
Example: Use string_agg with Distinct Values in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
team TEXT,
name TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 'Doug', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 'Chad', 12);
INSERT INTO athletes VALUES (0003, 'Mavs', 'Doug', 23);
INSERT INTO athletes VALUES (0004, 'Mavs', 'Mike', 23);
INSERT INTO athletes VALUES (0005, 'Hawks', 'Tony', 29);
INSERT INTO athletes VALUES (0006, 'Hawks', 'Zach', 14);
INSERT INTO athletes VALUES (0007, 'Hawks', 'Luke', 33);
INSERT INTO athletes VALUES (0008, 'Hawks', 'Luke', 33);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | name | points
-----------+-------+------+--------
1 | Mavs | Doug | 15
2 | Mavs | Chad | 12
3 | Mavs | Doug | 23
4 | Mavs | Mike | 23
5 | Hawks | Tony | 29
6 | Hawks | Zach | 14
7 | Hawks | Luke | 33
8 | Hawks | Luke | 33
Suppose that we would like to concatenate only the distinct strings from the name column together, grouping the results by the values in the team column.
We can use the following syntax to do so:
SELECT team, string_agg(DISTINCT (name), ', ') AS player_names
FROM athletes
GROUP BY team;
team | player_names
-------+------------------
Hawks | Luke, Tony, Zach
Mavs | Chad, Doug, Mike
Notice that the resulting table contains a new column named player_names that contains the concatenated strings from the player column, grouped by the values in the team column.
Notice that only distinct strings are returned in the player_names column.
For example, the name “Luke” appears twice on the Hawks team, but this name only appears once in the concatenated string since we used the DISTINCT command to specify that we only wanted to return distinct strings.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Concatenate Strings Using GROUP BY
PostgreSQL: How to Find Position of Character in String
PostgreSQL: How to Replace All Occurrences of String in Text Field
PostgreSQL: How to Replace Multiple Strings in a Column
