unnest()

A system function for expanding an array to a set of rows

unnest() is a system function for expanding an array, or combination of arrays, to a set of rows.

unnest() was added in PostgreSQL 8.4.

Usage

unnest ( anyarray ) → setof anyelement

Change history

Examples

Basic usage of unnest():

postgres=# SELECT unnest(ARRAY[1,2,3]);
 unnest 
--------
      1
      2
      3
(3 rows)

postgres=# SELECT unnest('{1,2,3}'::INT[]);
 unnest 
--------
      1
      2
      3
(3 rows)

Expand multiple arrays to a set of rows:

postgres=# SELECT * FROM unnest(ARRAY[1,2,3], ARRAY['foo','bar','baz']) AS x(id, val);
 id | val 
----+-----
  1 | foo
  2 | bar
  3 | baz
(3 rows)

Note that this variant is only available in a FROM clause.

Expand multiple arrays of different lengths to a set of rows:

postgres=# SELECT * FROM unnest(ARRAY[1,2], ARRAY['foo','bar','baz','boo'], ARRAY['a','b','c']) AS x(id, val);
 id | val | unnest 
----+-----+--------
  1 | foo | a
  2 | bar | b
    | baz | c
    | boo | 
(4 rows)

Categories

System function