Contents
array_agg()
A function which collects its input values into an array
array_agg()
is a system function which collects its input values into an array.
array_agg()
was added in PostgreSQL 8.4.
Usage
array_agg (anynonarray
) →anyarray
array_agg (anyarray
) →anyarray
Change history
- PostgreSQL 8.4
- added (commit 0a687c8f)
Examples
A simple, contrived example aggregating multiple rows into a single array of concatenated values:
postgres=# SELECT array_agg(x || ' / ' || y) FROM (values ('foo', 'bar'), ('bar', 'baz') ) _(x, y); array_agg ----------------------- {"foo / bar","bar / baz"} (1 row)
A further, contrived example aggregating values from multiple rows into sets:
postgres=# SELECT i, array_agg(x || ' / ' || y) FROM (values (1, 'foo', 'bar'), (2, 'boo', 'hoo'), (2, 'bar', 'baz') ) _(i, x,y) GROUP BY i; i | array_agg ---+--------------------------- 1 | {"foo / bar"} 2 | {"boo / hoo","bar / baz"} (2 rows)
Using ORDER BY
(recommended) to control the order in which the generated array is populated:
postgres=# SELECT i, array_agg(x || ' / ' || y ORDER BY x,y) FROM (values (1, 'foo', 'bar'), (2, 'zoo', 'yum'), (2, 'boo', 'hoo'), (2, 'bar', 'baz') ) _(i, x,y) GROUP BY i; i | array_agg ---+--------------------------------------- 1 | {"foo / bar"} 2 | {"bar / baz","boo / hoo","zoo / yum"} (2 rows)
References
- PostgreSQL documentation: General-Purpose Aggregate Functions