string_agg function

View as Markdown

The string_agg(value, delimiter) aggregate function concatenates the non-null input values (i.e. value) into text. Each value after the first is preceded by its corresponding delimiter, where null values are equivalent to an empty string. The input values to the aggregate can be filtered.

Syntax

string_agg ( <value>, <delimiter>
  [ORDER BY <col_ref> [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]]
)
[FILTER (WHERE <filter_clause>)]

Syntax element	Description
`<value>`	The values to concatenate.
`<delimiter>`	The value to precede each concatenated value.
ORDER BY `<col_ref>` [ASC \| DESC] [NULLS FIRST \| NULLS LAST] [, …]	Optional. Specifies the ordering of values within the aggregation. If not specified, incoming rows are not guaranteed any order.
FILTER (WHERE `<filter_clause>`)	Optional. Specifies which rows are sent to the aggregate function. Rows for which the `<filter_clause>` evaluates to true contribute to the aggregation. See Aggregate function filters for details.

Signatures

Parameter	Type	Description
value	`text`	The values to concatenate.
delimiter	`text`	The value to precede each concatenated value.

Return value

string_agg returns a text value.

Any ORDER BY applied before the aggregate function is evaluated, such as in a feeding subquery, is ignored. Unless ORDER BY is included in the aggregate function call itself, the order in which the values are aggregated is unspecified.

Usage in dataflows

While string_agg is available in Materialize, materializing views using it is considered an incremental view maintenance anti-pattern. Any change to the data underlying the function call will require the function to be recomputed entirely, discarding the benefits of maintaining incremental updates.

Instead, we recommend that you materialize all components required for the string_agg function call and create a non-materialized view using string_agg on top of that. That pattern is illustrated in the following statements:

CREATE MATERIALIZED VIEW foo_view AS SELECT * FROM foo;
CREATE VIEW bar AS SELECT string_agg(foo_view.bar, ',');

Examples

SELECT string_agg(b, ',' ORDER BY a DESC) FROM table;