CODEWITHSUNDEEP
sqlpostgresql
Kaervas
Kaervas

Reputation: 113

POSTGRESQL: Create table as Selecting specific type of columns

I have a table with mixed types of data (real, integrer, character ...) but i would only recover columns that have real values.

I can construct this:

SELECT 'SELECT ' || array_to_string(ARRAY(
select 'o' || '.' || c.column_name 
from information_schema.columns as c
where table_name = 'final_datas'
and c.data_type = 'real'), ',') || ' FROM final_datas as o' As sqlstmt

that gives that:

"SELECT o.random,o.struct2d_pred2_num,o.pfam_num,o.transmb_num [...] FROM final_datas as o"

The i would like to create a table with these columns. Of course, do this, doesn't work:

create table table2 as (
SELECT 'SELECT ' || array_to_string(ARRAY(
select 'o' || '.' || c.column_name 
from information_schema.columns as c
where table_name = 'final_datas'
and c.data_type = 'real'), ',') || ' FROM final_datas as o' As sqlstmt
)

Suggestions?

Upvotes: 1

Views: 521

Answers (1)

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10693

You need to generate the whole CREATE TABLE statement as dynamic SQL:

SELECT 'CREATE TABLE table2 AS SELECT ' || array_to_string(ARRAY(
select 'o' || '.' || c.column_name 
from information_schema.columns as c
where table_name = 'final_datas'
and c.data_type = 'real'), ',') || ' FROM final_datas as o' As sqlstmt

The result can be run with EXECUTE sqlstmt;

Upvotes: 1

Related Questions