All we need is an easy explanation of the problem, so here it is.
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.
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 ;
"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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂