PostgreSQL array_agg() Function

The PostgreSQL array_agg() function is an aggregate function that returns an array containing all specified values ​​in a group.

array_agg() Syntax

Here is the syntax of the PostgreSQL array_agg() function:

array_agg(expr) -> ARRAY

Typically, we use the array_agg() function like:

SELECT array_agg(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr

Required. A column name or expression.

Return value

The PostgreSQL array_agg() function returns an array that contains all the values of the specified expression or column in the specified group, including null.

array_agg() Examples

To demonstrate usages of the PostgreSQL array_agg() function, we simulate a temporary table using the following statement with UNION and SELECT:

SELECT 'Tim' name, 'Football' hobby
UNION
SELECT 'Tim' name, 'Baseball' hobby
UNION
SELECT 'Tom' name, 'Piano' hobby
UNION
SELECT 'Tom' name, 'violin' hobby;

 name |  hobby
------+----------
 Tom  | violin
 Tom  | Piano
 Tim  | Football
 Tim  | Baseball
(4 rows)

Here, we have some rows about the user’s hobbies. The name column is the user’s name, and the hobby column is a hobby of the user.

Suppose, if you want to put all the hobbies of each user in an array (rows with the same name count as one user), you can use the GROUP BY clause to group rows by name and use the array_agg() function to aggregate the hobbies in each group to an array. The following statement complete this requirement:

SELECT
    t.name,
    array_agg(t.hobby) hobbies
FROM (
    SELECT 'Tim' name, 'Football' hobby
    UNION
    SELECT 'Tim' name, 'Baseball' hobby
    UNION
    SELECT 'Tom' name, 'Piano' hobby
    UNION
    SELECT 'Tom' name, 'violin' hobby
) t
GROUP BY t.name;

 name |       hobbies
------+---------------------
 Tim  | {Football,Baseball}
 Tom  | {violin,Piano}
(2 rows)

Here, the hobbies column is of array type that contains all of a user’s hobbies.