By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- single field is unnested / decomposed automatically
SELECT n FROM unnest (ARRAY[1,2]) n;
SELECT n FROM unnest (ARRAY[1,2]) n(n);
SELECT n FROM unnest (ARRAY[1,2]) t(n);
SELECT t FROM unnest (ARRAY[1,2]) t(n); -- except output column name is "t"
| n |
|---|
| 1 |
| 2 |
SELECT 2
| n |
|---|
| 1 |
| 2 |
SELECT 2
| n |
|---|
| 1 |
| 2 |
SELECT 2
| t |
|---|
| 1 |
| 2 |
SELECT 2
-- but not with more than one fields
SELECT t FROM unnest (ARRAY[1,2,3]) WITH ORDINALITY t(n); -- requires 9.4
| t |
|---|
| (1,1) |
| (2,2) |
| (3,3) |
SELECT 3
-- registered composite type
CREATE TYPE my_type AS (id int, txt text);
CREATE TYPE
-- same: more than one field, so not unnested:
SELECT n FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) n;
| n |
|---|
| (1,foo) |
| (2,bar) |
SELECT 2
-- Guess the result!
SELECT n FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) n(n);
SELECT n FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) t(n);
SELECT * FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) t(n);
| n |
|---|
| 1 |
| 2 |
SELECT 2
| n |
|---|
| 1 |
| 2 |
SELECT 2
| n | txt |
|---|---|
| 1 | foo |
| 2 | bar |
SELECT 2
-- However (because only a single wrapper is unnested):
SELECT t FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) t(n);
| t |
|---|
| (1,foo) |
| (2,bar) |
SELECT 2
-- In Postgres 9.4+ with the new form of `unnest()` taking multiple array arguments:
SELECT *
FROM unnest (ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]
, ARRAY[(3, 'baz')::my_type, (4, 'bak')::my_type]) n;
| id | txt | id | txt |
|---|---|---|---|
| 1 | foo | 3 | baz |
| 2 | bar | 4 | bak |
SELECT 2
-- column aliases for only the first two columns
SELECT *
FROM unnest (ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]
, ARRAY[(3, 'baz')::my_type, (4, 'bak')::my_type]) n(a, b);
| a | b | id | txt |
|---|---|---|---|
| 1 | foo | 3 | baz |
| 2 | bar | 4 | bak |
SELECT 2
-- column aliases for for all output columns
SELECT *
FROM unnest(ARRAY[(1,'foo')::my_type, (2,'bar')::my_type]
, ARRAY[(3,'baz')::my_type, (4,'bak')::my_type]) n(a,b,c,d);
| a | b | c | d |
|---|---|---|---|
| 1 | foo | 3 | baz |
| 2 | bar | 4 | bak |
SELECT 2