Contents
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
- PostgreSQL 9.4
- multiple argument form added (commit 784e762e)
- PostgreSQL 8.4
- added (commit c889ebce)
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)
References
- PostgreSQL documentation: Array Functions