array_agg()

Collects all the input values, including nulls, into an array.

Signature

array_agg ( anynonarray ORDER BY input_sort_columns ) → anyarray
array_agg ( anyarray ORDER BY input_sort_columns ) → anyarray

Examples

SELECT array_agg(product ORDER BY id) FROM orders;

                    array_agg                    
-------------------------------------------------
 {keyboard,monitor,keyboard,mouse,monitor,cable}
(1 row)

Run against PostgreSQL 18.6 on 2026-09-13. This is the output we got, not the documentation’s.

In other engines

PostgreSQL	array_agg
MySQL	JSON_ARRAYAGG(x)
SQL Server	no direct equivalent
Oracle	no direct equivalent
SQLite	json_group_array(x)

PostgreSQL is the only one of the five with a real array type, so this is the clearest case of a function that cannot be ported — everywhere else the answer is a JSON array or a delimited string, and the client has to parse it back.

Want to run this against your own PostgreSQL server from an iPhone, iPad or Mac? These are ours — every connection goes straight from the device to your database, with no account and no server of ours in between.

Postgres & FriendsPostgreSQL and the open-source engines around it

Signature and description adapted from the PostgreSQL manual, used under the PostgreSQL License. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. This page is not affiliated with the PostgreSQL Global Development Group.