PostgreSQL string_agg() Function

The PostgreSQL string_agg() function is an aggregate function that returns a string containing all the specified values ​​in a group.

string_agg() Syntax

Here is the syntax of the PostgreSQL string_agg() function:

string_agg(expr, delimiter TEXT) -> TEXT

Typically, we use the string_agg() function like:

SELECT string_agg(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr

Required. A column name or expression in which all values ​​will be concatenated.

delimiter

Required. The delimiter.

Return value

The PostgreSQL string_agg() function returns a string that contains all the values ​​in the specified expression or column in the specified group, including null, where multiple values ​​are separated by delimiter.

string_agg() Examples

To demonstrate usages of the PostgreSQL string_agg() function, we simulate a temporary table using the following statement with UNION and SELECT:

SELECT 'Tim' name, 'Football' hobby
UNION
SELECT 'Tim' name, 'Baseball' hobby
UNION
SELECT 'Tom' name, 'Piano' hobby
UNION
SELECT 'Tom' name, 'violin' hobby;

 name |  hobby
------+----------
 Tom  | violin
 Tom  | Piano
 Tim  | Football
 Tim  | Baseball
(4 rows)

Here, we have some rows about the user’s hobbies. The name column is the user’s name, and the hobby column is a hobby of a user.

Suppose, to put all the hobbies of each user in a string (separated by commas) for each user (users with the same name count as one user), you can use the GROUP BY clause to group all rows by name, and use the string_agg() function to combine the hobby values ​​in each group into a string. The following statement fulfills this requirement:

SELECT
    t.name,
    string_agg(t.hobby, ',') hobbies
FROM (
    SELECT 'Tim' name, 'Football' hobby
    UNION
    SELECT 'Tim' name, 'Baseball' hobby
    UNION
    SELECT 'Tom' name, 'Piano' hobby
    UNION
    SELECT 'Tom' name, 'violin' hobby
) t
GROUP BY t.name;

 name |      hobbies
------+-------------------
 Tim  | Football,Baseball
 Tom  | violin,Piano
(2 rows)

Here, the hobbies column is a string containing all of a user’s hobbies, separated by commas ,.

Of course, you can also use the separator #, as follows:

SELECT
    t.name,
    string_agg(t.hobby, '#') hobbies
FROM (
    SELECT 'Tim' name, 'Football' hobby
    UNION
    SELECT 'Tim' name, 'Baseball' hobby
    UNION
    SELECT 'Tom' name, 'Piano' hobby
    UNION
    SELECT 'Tom' name, 'violin' hobby
) t
GROUP BY t.name;

 name |      hobbies
------+-------------------
 Tim  | Football#Baseball
 Tom  | violin#Piano
(2 rows)