PostgreSQL string_agg() Function

The PostgreSQL string_agg() aggregate combines non-NULL text values into one string. It inserts the delimiter between values. Unless you specify an order inside the aggregate call, PostgreSQL does not guarantee the order of the concatenated values.

string_agg() Syntax

string_agg(value, delimiter [ORDER BY sort_expression])

• value: A text expression to concatenate. NULL values are skipped.
• delimiter: Text inserted between consecutive non-NULL values.
• ORDER BY sort_expression: Optional. Controls the order of values in the result string.

The function returns text. If the aggregate receives no non-NULL values, it returns SQL NULL.

string_agg() Example

This query combines each person’s hobbies in a defined order. The NULL hobby is omitted, and the outer ORDER BY sorts the result rows by name:

WITH hobbies(name, hobby, sort_order) AS (
    VALUES
        ('Tim', 'Football', 1),
        ('Tim', NULL::text, 2),
        ('Tim', 'Baseball', 3),
        ('Tom', 'Piano', 1),
        ('Tom', 'Violin', 2)
)
SELECT
    name,
    string_agg(hobby, ', ' ORDER BY sort_order) AS hobbies
FROM hobbies
GROUP BY name
ORDER BY name;

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

The ORDER BY inside string_agg() orders values within each group. The final ORDER BY name orders the groups in the query result; it does not set the order of values inside each string.

If a query can produce no non-NULL values and you need an empty string instead of NULL, use COALESCE, for example:

COALESCE(string_agg(hobby, ', ' ORDER BY sort_order), '')

For other grouped results, compare array_agg(), json_agg(), and jsonb_agg().

For more details, see PostgreSQL’s official aggregate function documentation.