Equivalent to unpivot() in PostgreSQL

Question

Is there a unpivot equivalent function in PostgreSQL?

Answer 1

Create an example table:

CREATE TEMP TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil');

You can 'unpivot' or 'uncrosstab' using UNION ALL:

SELECT id,
       'a' AS colname,
       a AS thing
FROM foo
UNION ALL
SELECT id,
       'b' AS colname, 
       b AS thing
FROM foo
UNION ALL
SELECT id, 
       'c' AS colname,
       c AS thing
FROM foo
ORDER BY id;

This runs 3 different subqueries on foo, one for each column we want to unpivot, and returns, in one table, every record from each of the subqueries.

But that will scan the table N times, where N is the number of columns you want to unpivot. This is inefficient, and a big problem when, for example, you're working with a very large table that takes a long time to scan.

Instead, use:

SELECT id,
       unnest(array['a', 'b', 'c']) AS colname,
       unnest(array[a, b, c]) AS thing
FROM foo
ORDER BY id;

This is easier to write, and it will only scan the table once.

array[a, b, c] returns an array object, with the values of a, b, and c as it's elements. unnest(array[a, b, c]) breaks the results into one row for each of the array's elements.

Hope that helps!