Equivalent to unpivot() in PostgreSQL

by

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!

More Related Contents:

  1. What to do with null values when modeling and normalizing?
  2. Postgres: INSERT if does not exist already
  3. Truncating all tables in a Postgres database
  4. Making sense of Postgres row sizes
  5. How to export table as CSV with headings on Postgresql?
  6. What’s the PostgreSQL datatype equivalent to MySQL AUTO INCREMENT?
  7. Time zone names with identical properties yield different result when applied to timestamp
  8. Permission denied for relation
  9. How do I get a column with consecutive, increasing numbers, without having any numbers missing?
  10. Execute a dynamic crosstab query
  11. Copy a table from one database to another in Postgres
  12. Can I automatically create a table in PostgreSQL from a csv file with headers?
  13. User defined variables in PostgreSQL
  14. PostgreSQL query very slow with limit 1
  15. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  16. Postgres query optimization (forcing an index scan)
  17. psql: FATAL: database “” does not exist
  18. Difference between timestamps with/without time zone in PostgreSQL
  19. Postgres FOR LOOP
  20. Omitting the double quote to do query on PostgreSQL
  21. Storing Images in PostgreSQL
  22. Psql could not connect to server: No such file or directory, 5432 error?
  23. Postgres: Distinct but only for one column
  24. In PostgreSQL, how to insert data with COPY command?
  25. Maximum characters in labels (table names, columns etc)
  26. Docker – Postgres and pgAdmin 4 : Connection refused
  27. Configuration parameter work_mem in PostgreSQL on Linux
  28. How to work with PGpoint for Geolocation using PostgreSQL?
  29. Spring Docker container cannot access Postgres Docker container
  30. PostgreSQL composite primary key

Leave a Comment