PostgreSQL How to Convert Array to Rows Easily

Use the unnest() function in PostgreSQL to convert an array to rows, for example: SELECT unnest(ARRAY[1,2,3]); returns each array element as a separate row.

Examples

InputARRAY[1, 2, 3]

Output1 2 3

InputARRAY['apple', 'banana', 'cherry']

Outputapple banana cherry

InputARRAY[]::integer[]

Output

How to Think About It

To convert an array to rows, think of each element in the array as an item you want to list vertically. PostgreSQL provides the unnest() function that takes an array and returns each element as a separate row, making it easy to work with array data in a table-like format.

Algorithm

1

Get the input array you want to convert.

2

Use the <code>unnest()</code> function to expand the array elements into individual rows.

3

Return the result set where each row contains one element from the array.

Code

postgresql

SELECT unnest(ARRAY[10, 20, 30]) AS element;

Output

 element 
---------
      10
      20
      30
(3 rows)

Dry Run

Let's trace ARRAY[10, 20, 30] through the unnest() function

1

Input array

ARRAY[10, 20, 30]

2

Apply unnest()

unnest(ARRAY[10, 20, 30])

3

Output rows

Rows with values 10, 20, and 30 each in separate rows

element
10
20
30

Why This Works

Step 1: Why use unnest()?

The unnest() function is designed to take an array and return each element as a separate row, which is exactly what we need to convert arrays to rows.

Step 2: How it works internally

PostgreSQL treats the array as a list and iterates over each element, outputting one row per element.

Step 3: Result format

The output is a set of rows, each containing one element from the original array, making it easy to join or filter like normal table data.

Alternative Approaches

Using generate_series with array indexes

postgresql

SELECT arr[i] AS element FROM generate_series(1, array_length(arr, 1)) AS s(i), (SELECT ARRAY[1,2,3] AS arr) AS t;

This method uses indexes to access array elements but is more complex and less direct than unnest().

Using lateral join with unnest

postgresql

SELECT element FROM (SELECT ARRAY['a','b','c'] AS arr) AS t CROSS JOIN LATERAL unnest(arr) AS element;

This is useful when unnesting arrays stored in table columns alongside other data.

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

Time Complexity

The time is O(n) because unnest() processes each element of the array once to produce rows.

Space Complexity

Space is O(n) as the output contains one row per array element, requiring memory proportional to the array size.

Which Approach is Fastest?

Using unnest() is generally fastest and simplest; alternatives like generate_series add overhead and complexity.

Approach	Time	Space	Best For
unnest()	O(n)	O(n)	Simple and direct array to rows conversion
generate_series with indexes	O(n)	O(n)	When you need element indexes or complex processing
lateral join with unnest	O(n)	O(n)	Unnesting arrays stored in table columns with other data