Unpivot in Potgresql

Question

How can I unpivot in Postgresql without using UNION? I have more than 100 columns, and I am looking for a neat way to do it.

Given table:

id    c1      c2      c3
1      X       Y       Z
2      A       B       C
3      Y       C       Z

Desired table:

id   col
1     X
1     Y
1     Z
2     A
2     B
2     C
3     Y
3     C
3     Z

Answer 1

Use jsonb functions:

select id, value as col
from my_table
cross join jsonb_each_text(to_jsonb(my_table))
where key <> 'id';

 id | value 
----+-------
  1 | X
  1 | Y
  1 | Z
  2 | A
  2 | B
  2 | C
  3 | Y
  3 | C
  3 | Z
(9 rows)

Db<>Fiddle.

---

In Postgres 9.3 or 9.4 use to_json() and json_each_text().

In versions 9.1 or 9.2 install hstore:

create extension if not exists hstore;

select id, value as col
from my_table
cross join each(hstore(my_table))
where key <> 'id';