POSTGRESQL: Create table as Selecting specific type of columns

Question

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?

Look into the pg_catalog for this. Run psql -E, then \d final_datas within it, and you'll see the relevant queries that you need to run. — Denis de Bernardy
– Denis de Bernardy, Commented Nov 24, 2013 at 20:50

Kombajn zbożowy · Accepted Answer · 2013-11-24 21:31:21Z

1

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;

answered Nov 24, 2013 at 21:31

Kombajn zbożowy

10.8k5 gold badges33 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

POSTGRESQL: Create table as Selecting specific type of columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related