In PostgreSQL, the array_agg() function is an aggregate function which returns an array containing values of what is between the brackets ().

Structure

SELECT array_agg(expression)
FROM TABLE
WHERE [CONSTRAINTS]
GROUP BY [EXPRESSION]

Example

Suppose we want to output the sporting equipment that each individual has borrowed. Here is how we would do that:

SELECT b.name, array_agg(e.name) as equipment from borrowers b
JOIN equipment_borrowed e ON e.id = b.id
WHERE b.active = 1
GROUP BY b.name;

Output

name	equipment
Tom	{Basketball,Soccer ball}
Jenn	{Volleyball,Tennis equipment}
Tony	{Hockey stick,Hockey puck}