STRING_AGG

​

Syntax

​

String agg (aggregate)

string_agg([DISTINCT] value [, delimiter] [, max_elements] [ORDER BY ordering_key [, ...]])

• A literal row limit can be the second argument when the delimiter is omitted.

​

String agg window

string_agg(value,delimiter,max_elements) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

• Window usage requires STRING_AGG with all three arguments.
• Use a frame that includes all rows in the group or starts at the first row (UNBOUNDED PRECEDING). Sliding frames that drop earlier rows are not supported.

​

Arguments

​

String agg

​

String agg window

​

Returns

• Type: STRING
• NULL behavior: NULL values are skipped; NULL delimiter is rejected.
• Empty input: Returns NULL on empty/all-NULL input.

​

Properties

​

Usage notes

• max_elements limits input elements, not output characters or bytes; -1 means unlimited.
• If omitted, the delimiter is ”. Supply a STRING literal; a delimiter expression currently uses ” instead of its value.
• LISTAGG has no row-limit argument. STRING_AGG accepts a literal row limit as its second or third argument.
• DISTINCT removes duplicates in grouped aggregation. Do not use it to remove duplicates in a window call.

​

Examples

​

STRING_AGG row limit

WITH sample(v) AS (VALUES ('c'), ('a'), ('b'))
SELECT string_agg(v, ',', 2 ORDER BY v NULLS LAST) AS result
FROM sample;

​

STRING_AGG over all rows in each group

WITH sample(id, v) AS (VALUES (1, 'a'), (2, 'b'))
SELECT id, string_agg(v, ',', -1) OVER (ORDER BY id NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS result
FROM sample
ORDER BY id NULLS LAST;

​

Limitations

• Sliding frames that drop earlier rows and RANGE frames with numeric or time offsets are not supported.
• DISTINCT WITHIN GROUP ordering must use the aggregated expression.
• LISTAGG window calls currently fail. Use STRING_AGG(value, delimiter, max_elements) for window usage.