I want to extract columns depending on their data type from a table. From this table I want to only end up with columns containing only integers.

This is the desired output:

How would I go about doing this?

I have tried to use string_agg() to use the column names in a select statement but it did not create the output I desired.

select( 
select
string_agg(column_name, ',')
from information_schema.columns
where table_name = 'table_name' and data_type = 'integer')
from table_name

CodePudding user response：

DECLARE @sql VARCHAR(max);
SELECT 'SELECT ' || string_agg(c.column_name, ', ') 
    || 'FROM ' || c.table_schema || '.' || c.table_name 
FROM information_schema."columns" c
WHERE 
    c.table_schema = 'public' 
    AND c.table_name = 'books'
    AND c.data_type = 'integer'
GROUP BY c.table_schema, c.table_name;

EXECUTE @sql; -- or try EXECUTE(@sql)

CodePudding user response：

You must use dynamic SQL for do it.

Sample:

select 'select ' || string_agg(column_name, ', ') || ' from ' || table_schema || '.' || table_name 
from information_schema."columns"
where 
    table_schema = 'public' 
    and table_name = 'books'
    and data_type = 'integer'
group by table_schema, table_name

Result:

After then execute this using EXECUTE command:

execute 'select id, bookcode, maxcode from public.books';