Often in PostgreSQL you may want to use the string_agg function to concatenate strings together and limit the total number of strings that are concatenated.
You can use the following syntax to do so:
SELECT team, array_to_string((array_agg(name))[1:3], ', ') AS player_names
FROM athletes
GROUP BY team;
This particular example will concatenate only the first three strings from the name column together into a new column named player_names using a comma as a separator.
By using the bracket notation [1:3] we are able to specify that we only want to concatenate the first three strings from the name column.
The following example shows how to use this syntax in practice.
Example: Use string_agg with Limited Number of Elements 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', 'Matt', 23);
INSERT INTO athletes VALUES (0004, 'Mavs', 'Adam', 40);
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', 'Walt', 17);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | name | points
-----------+-------+------+--------
1 | Mavs | Doug | 15
2 | Mavs | Chad | 12
3 | Mavs | Matt | 23
4 | Mavs | Adam | 40
5 | Hawks | Tony | 29
6 | Hawks | Zach | 14
7 | Hawks | Luke | 33
8 | Hawks | Walt | 17
Suppose that we would like to concatenate only the first three 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, array_to_string((array_agg(name))[1:3], ', ') AS player_names
FROM athletes
GROUP BY team;
team | player_names
-------+------------------
Mavs | Doug, Chad, Matt
Hawks | Tony, Zach, Luke
The resulting table contains a new column named player_names that contains the first three strings from the player column, grouped by the values in the team column.
Although each team has four players in the table, only the first three names that appear for each team are concatenated together since we used the [1:3] notation.
Feel free to change 3 to any value that you’d like to instead set a different limit on the number of elements that are concatenated together.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Use string_agg with Distinct Values
PostgreSQL: How to Use string_agg with Multiple Columns
PostgreSQL: How to Use string_agg with Order By
