How to Use array_agg in PostgreSQL: Syntax and Examples

In PostgreSQL, use the array_agg function to combine multiple row values into a single array. It is often used with GROUP BY to collect grouped values into arrays. The syntax is array_agg(expression), where expression is the column or value to aggregate.

Syntax

The array_agg function collects values from multiple rows into a single array.

• array_agg(expression): Aggregates the values of expression into an array.
• expression: The column or value you want to combine into an array.
• Usually used with GROUP BY to aggregate values per group.

sql

SELECT array_agg(column_name) FROM table_name [GROUP BY grouping_column];

Example

This example shows how to use array_agg to collect all product names per category into arrays.

sql

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  category TEXT,
  product_name TEXT
);

INSERT INTO products (category, product_name) VALUES
('Fruits', 'Apple'),
('Fruits', 'Banana'),
('Fruits', 'Orange'),
('Vegetables', 'Carrot'),
('Vegetables', 'Broccoli');

SELECT category, array_agg(product_name) AS products
FROM products
GROUP BY category
ORDER BY category;

Output

 category   |         products         
------------+--------------------------
 Fruits     | {Apple,Banana,Orange}
 Vegetables | {Carrot,Broccoli}
(2 rows)

Common Pitfalls

Common mistakes when using array_agg include:

• Not using GROUP BY when aggregating per group, which results in a single array for the whole table.
• Expecting array_agg to return sorted arrays by default; it does not sort unless you specify ORDER BY inside the function.
• Using array_agg on columns with NULL values without handling them, which includes NULL in the result array.

Example of sorting inside array_agg:

sql

SELECT category, array_agg(product_name ORDER BY product_name) AS products_sorted
FROM products
GROUP BY category
ORDER BY category;

Output

 category   |         products_sorted         
------------+---------------------------------
 Fruits     | {Apple,Banana,Orange}
 Vegetables | {Broccoli,Carrot}
(2 rows)

Quick Reference

Feature	Description
Function	array_agg(expression)
Purpose	Aggregate multiple values into a single array
Common Use	Used with GROUP BY to collect grouped values
Ordering	Use ORDER BY inside array_agg to sort results
Null Handling	Includes NULLs unless filtered or handled

Key Takeaways

Use array_agg(expression) to combine multiple row values into an array in PostgreSQL.

Always use GROUP BY when you want arrays per group, not for the whole table.

Add ORDER BY inside array_agg to get sorted arrays.

Be aware that NULL values are included in the array unless filtered out.

array_agg is useful for collecting related values into one array for easier processing.