Transpose rows to column in postgresql

user_14751861 0
Viewed 83
select distinct column_name
from information_schema.columns
where table_name like 'fea_var%';

This query gives me a list of 1222 distinct columns and I want to create one base table which has all the 1222 rows from this query as columns. fea_var% tables are just empty tables with columns.
So, the output should be an empty table with those 1222 columns.

postgresql
1 Answers

You can try this where all the columns will be of type text :

CREATE PROCEDURE create_table() LANGUAGE plpgsql AS $$
DECLARE
  column_list text ;
BEGIN
SELECT string_agg(DISTINCT quote_ident(column_name) || ' text', ',')
  INTO column_list
  FROM information_schema.columns
 WHERE table_name LIKE 'fea_var%';

EXECUTE 'CREATE OR REPLACE TABLE newtable (' || column_list || ');' ;
END ; $$ ;

CALL create_table() ;
user_80600170
Related