How to Use UNNEST in PostgreSQL: Syntax and Examples

In PostgreSQL, UNNEST is used to expand an array into a set of rows, turning each element into a separate row. You use it in the FROM clause to convert array data into a table-like format for easier querying.

Syntax

The basic syntax of UNNEST is simple. You write UNNEST(array_expression) inside the FROM clause. This converts the array into a set of rows, one for each element.

• array_expression: The array you want to expand.
• Returns: A set of rows, each containing one element from the array.

sql

SELECT unnest(array[1, 2, 3]) AS element;

Output

 element 
---------
       1
       2
       3
(3 rows)

Example

This example shows how to use UNNEST to turn an array of integers into rows. It demonstrates how each array element becomes a separate row in the result.

sql

SELECT unnest(array['apple', 'banana', 'cherry']) AS fruit;

Output

  fruit  
---------
 apple
 banana
 cherry
(3 rows)

Common Pitfalls

One common mistake is trying to use UNNEST without the FROM clause or expecting it to work like a normal function returning a single value. Also, when unnesting multiple arrays, they must have the same length to avoid unexpected results.

Wrong usage example:

SELECT unnest(array[1,2,3]) + 1;

This will cause an error because UNNEST returns a set, not a scalar value.

Correct usage:

SELECT element + 1 FROM unnest(array[1,2,3]) AS element;

Quick Reference

Feature	Description
UNNEST(array)	Expands an array into a set of rows
Use in FROM clause	Place UNNEST inside FROM to treat array elements as rows
Multiple arrays	Can unnest multiple arrays together if they have equal length
Alias usage	Use alias to name the output column(s) for clarity

Key Takeaways

Use UNNEST in the FROM clause to convert arrays into rows.

Each element of the array becomes one row in the result set.

When unnesting multiple arrays, ensure they have the same length.

Always alias the output of UNNEST for clear column names.

UNNEST returns a set, so it cannot be used like a scalar function.