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