2

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.

Improve this question
5
  • 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!
    – Vérace
    Dec 15, 2021 at 21:29
  • 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.
    – Reshmi Nair
    Dec 16, 2021 at 9:24
  • 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!
    – Vérace
    Dec 16, 2021 at 10:16
  • 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.
    – Reshmi Nair
    Dec 16, 2021 at 10:43
  • Sounds good - best...!
    – Vérace
    Dec 16, 2021 at 10:49

1 Answer 1

Reset to default
3

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.

Improve this answer
1
  • 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.
    – Reshmi Nair
    Dec 17, 2021 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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