PostgreSQL – How to Unnest All Columns from a Table

postgresql

I have a table, which was loaded dynamically from an external job. This table has a changing number of coulmns from time to time.
I want to unnest all the columns with the coulmn name as 'Key' and the value as 'Value':

SELECT
   unnest(array[(select array_agg( column_name::text )from information_schema.columns where table_name = 'tableName')]) AS "Keys",
   unnest(array[tableName.col1, tablename.col2, ...]) AS "Values"
FROM tableName ;

The problem that I'm facing is that I need something to replace the second unnest Statement, because I don't know the names of the columns.

I thought I just use unnest(array[tableName.*]) as "Values"but that does not work.

Any ideas welcome.

Example:

create table mytest ( col_1 varchar(30), col_2 varchar(30));
insert into mytest values ('a1','b1');
insert into mytest values ('a2','b2');
insert into mytest values ('a3','b3');
SELECT
   unnest(array[(select array_agg( column_name::text )from information_schema.columns where table_name = 'mytest')]) AS "Key",
   unnest(array[mytest.col_1, mytest.col_2]) AS "Value"
FROM mytest ;

Results in:

"Key"   "Value"
col_1   a1
col_2   b1
col_1   a2
col_2   b2
col_1   a3
col_2   b3

which is fine.
But when I get a table of unknown numbers of columns I need something to put into the second unnest statement (unnest(array[tableName.col1, tablename.col2, ...])) so I thought somethng like tablename.* instead of tableName.col1, tablename.col2.

Best Answer

You can use JSON functions to do that for you:

select x.*
from mytest, jsonb_each_text(to_jsonb(mytest)) as x("key",value);

With your sample data, this returns:

key   | value
------+------
col_1 | a1   
col_2 | b1   
col_1 | a2   
col_2 | b2   
col_1 | a3   
col_2 | b3

to_jsonb(mytest) converts the whole row into a JSON value using the column names as keys and jsonb_each_text() is the unnesting of that JSON value.

Online example: https://rextester.com/DSNOIU44927

Related Question