PostgreSQL How to Convert Rows to Array Easily

Use the array_agg(column_name) function in PostgreSQL to convert multiple rows from a column into a single array.

Examples

InputTable: fruits with rows: apple, banana, cherry

Output{apple,banana,cherry}

InputTable: numbers with rows: 1, 2, 3, 4

Output{1,2,3,4}

InputEmpty table or no matching rows

Output{}

How to Think About It

To convert rows to an array, think of collecting all values from a column across multiple rows into one grouped list. PostgreSQL provides the array_agg() function that gathers these values into an array automatically.

Algorithm

1

Identify the column whose rows you want to convert to an array.

2

Use the <code>array_agg()</code> function on that column in a SELECT query.

3

Execute the query to get a single row with an array containing all the values.

Code

postgresql

CREATE TABLE fruits (name TEXT);
INSERT INTO fruits VALUES ('apple'), ('banana'), ('cherry');

SELECT array_agg(name) AS fruit_array FROM fruits;

Output

 fruit_array 
-------------
 {apple,banana,cherry}
(1 row)

Dry Run

Let's trace converting fruit names from rows to an array.

1

Input rows

Rows: 'apple', 'banana', 'cherry'

2

Apply array_agg()

Collect all names into one array

3

Output

Single array: {apple,banana,cherry}

name
apple
banana
cherry

Why This Works

Step 1: What array_agg() does

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

Step 2: Grouping values

It groups all values from the specified column without needing explicit grouping.

Step 3: Result format

The result is a one-row output with an array containing all collected values.

Alternative Approaches

Using ARRAY constructor with subquery

postgresql

SELECT ARRAY(SELECT name FROM fruits);

This also returns an array of values but uses a subquery inside ARRAY constructor instead of array_agg().

Using string_agg() then string_to_array()

postgresql

SELECT string_to_array(string_agg(name, ','), ',') FROM fruits;

This converts rows to a comma-separated string first, then splits it back to an array; less efficient but useful for custom delimiters.

Complexity: O(n) time, O(n) space

Time Complexity

The function scans all n rows once to collect values, so time complexity is O(n).

Space Complexity

It stores all collected values in an array, so space complexity is O(n) proportional to the number of rows.

Which Approach is Fastest?

array_agg() is generally fastest and most readable; ARRAY constructor with subquery is similar but slightly less direct.

Approach	Time	Space	Best For
array_agg()	O(n)	O(n)	Simple and efficient aggregation
ARRAY constructor with subquery	O(n)	O(n)	Alternative syntax, similar performance
string_agg() + string_to_array()	O(n)	O(n)	When custom delimiters or string manipulation needed