0

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.

  • 1
    It's unclear to me what you are trying to achieve. Please edit your question (by clicking on the edit link below it) and add some sample data and the expected output based on that data. Formatted text please, no screen shots. (edit your question - do not post code or additional information in comments) – a_horse_with_no_name Feb 27 at 15:31
  • I'm sure you know it and you can't do it because of external constraints, but it would be so much better to modelize your data correctly... Remember Pike's 5th rule... – Arkhena Feb 27 at 16:05
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.