STRING_AGG

Aggregate non-NULL values into a delimited string across grouped rows.

Category: stringReturns: VARCHARDialect: PostgreSql

Syntax

STRING_AGG(expr, delimiter)

Description

## Overview Aggregates values across grouped rows by concatenating them with the specified delimiter. STRING_AGG is the row-oriented counterpart of ARRAY_TO_STRING: it takes values from many rows and produces one string per group. Use it to build comma-separated lists of tags, names, IDs, or any other free-text column within a GROUP BY. The order of concatenation follows the input order, which is non-deterministic unless you impose an ORDER BY inside a subquery or use an ordered-set aggregate form. When output order matters (almost always, for stable reports and diffs), sort the input explicitly. ## Behavior - Returns NULL when every value in the group is NULL. - Returns an empty delimiter-free string when exactly one non-NULL value is present in the group. - Skips NULL values silently; the delimiter is not emitted around them. - Input order is undefined unless explicitly ordered. DeltaForge does not promise a deterministic order. - Non-string expressions are cast to their default text representation before concatenation. - Operates on Unicode code points; produces valid UTF-8 output. ## Compatibility - Matches the common SQL STRING_AGG semantics. - For the SQL-standard LISTAGG spelling, use STRING_AGG with a sorted subquery or rewrite the query using the LISTAGG form.

Parameters

Name	Type	Description
`expr`		Specifies the expression whose non-NULL values are concatenated across the grouped rows.
`delimiter`		Specifies the separator inserted between each aggregated value.

Examples

-- Aggregate names with a comma separator
SELECT STRING_AGG(name, ', ')
FROM (VALUES ('Alice'), ('Bob'), ('Carol')) AS t(name);  -- 'Alice, Bob, Carol'

-- NULL values are skipped
SELECT STRING_AGG(x, ',')
FROM (VALUES ('a'), (NULL), ('c')) AS t(x);  -- 'a,c'

-- Single value has no delimiter in the result
SELECT STRING_AGG(x, ',')
FROM (VALUES ('only')) AS t(x);  -- 'only'

-- All NULLs yield NULL, not an empty string
SELECT STRING_AGG(x, ',')
FROM (VALUES (NULL::VARCHAR), (NULL)) AS t(x);  -- NULL

-- Ordered aggregate using a subquery with ORDER BY
SELECT category, STRING_AGG(name, ', ') AS names
FROM (SELECT category, name FROM retail.products.catalog ORDER BY name) t
GROUP BY category;

-- Aggregate tags per customer
SELECT customer_id, STRING_AGG(tag, ';')
FROM retail.customers.tag_assignments
GROUP BY customer_id;

Pitfalls

STRING_AGG without ordering is non-deterministic. Diffs between runs will be noisy. Sort the input with ORDER BY in a subquery when output order matters.
All-NULL groups return NULL, not an empty string. Downstream logic that tests for empty output will miss these groups.
STRING_AGG can produce very large strings on wide groups. Cap group size or use ARRAY_AGG when you need a bounded representation.
STRING_AGG does not escape embedded delimiter characters. If any aggregated value contains the delimiter, splitting the result later will over-split.