Transpose rows to column in postgresql

Question

The following query gives me a list of 1222 distinct columns:

select distinct column_name from information_schema.columns where table_name like 'fea_var%';

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.

• Vérace: Hi, and welcome to dba.se! Could you explain why you want to do this? Anything north of 50 columns and I become nervous! There is a crosstab extension!
• Reshmi Nair: Thanks.Tried the crosstab extension but don't have administrator rights to install tablefunc module.I need this kind of a solution as I created a dynamic sql to create 132 temporary tables which get generated weekly from our model scoring processes. Now, these 132 tables have dynamically changing fields depending on the features used for a particular model. So, I needed a consolidated table which could have all the fields from these 132 temporary tables and then I insert values into the consolidated table, keeping nulls for columns not present in a particular feature variable table.
• Vérace: You should request that the module be installed - it's in the contrib section - that means that it's "approved" - not some dodgy code picked up off the internet! :-). Otherwise, I think you might have to "hand-craft" a script - get your list of fields - one per line in a file, then use sed to add a datatype and NOT NULL or whatever as required. Then run it manually? Sorry that I can't be more helpful, but I'm hoping this is a one-off? fea_var% - is this a biological/genetics issue? Check my profile!
• Reshmi Nair: No , not a biological problem. Feature variables used for the propensity model build for customers. The solution that I have finally found is to use pg_class and pg_namespace to get the oid for all these 132 tables that are like fea_var%. Then, using string_agg function to generate the create statement for the consolidated table and the insert into statements for the 132 tables.
• Vérace: Sounds good - best...!

Answer 1

I am not sure why you need to do this. Since your are querying information_schema I assume there are tables or a table which correspond to the 'fea_var%' condition. You could join these tables and set a condition that make sure no row is returned. Lets say you have two tables fea_var1 and fea_var2. Something like:

select * from fea_var1,fea_var2 where 1=2;

You should get all columns of both tables and no rows, if that is what you are looking for.

• Reshmi Nair: There are 132 such tables and there are repetitive columns too. Do you have a solution to get only the distinct column names? Separating by commas, usually gives the <table_name>.<column_name> as column names.