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

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)

Categories

System function