Reshape table by converting columns to rows
There is no way to have the column names being evaluated as columns, as pointed out in this question https://stackoverflow.com/questions/15800367/select-columns-with-particular-column-names-in-postgresql

The way to do it, is using to_jsonb, as suggested in unnest all columns from a given table, like:

select foo.id, x.*
from foo, jsonb_each_text(to_jsonb(foo)) as x(colname,colvalue)
where x.colname ~ '^[a-z]$';


In my specific case, I was trying to convert all data related to Corona Virus. The original data is available at: https://github.com/CSSEGISandData/COVID-19/blob/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv. I've imported it as table covid.

It has many columns named after the date, like: '1/22/20','1/23/20','1/24/20','1/25/20','1/26/20', .... The first columns are related with the location, so I want to keep them in the reshaped table.

The final query I'm using is:

select id, geom, "province/state" , "country/region", to_date( reportdate, 'MM/DD/YY'), nofcases
from covid, jsonb_each_text(to_jsonb(covid)) as x(reportdate,nofcases)
where x.reportdate ~ '[0-9]+'
order by 5;