How to extract columns by data type?

Question

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.

Price.	Food	Quantity
5	Bread	6
3	Cereal	7

This is the desired output:

Price.	Quantity
5	6
3	7

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

Related threads: [1](https://stackoverflow.com/q/23929707/5298879), [2](https://stackoverflow.com/q/11740256/5298879). Right now you're likely selecting a string expecting it to be inlined and executed as if you typed it in that place of the query. To do something like that, you'd have to construct the query as text and then use PL/pgSQL [`execute`](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) in a [function](https://www.postgresql.org/docs/current/sql-createfunction.html) to run that query. — Zegarek, Oct 29 '22 at 18:20

score 0 · Answer 1 · answered Nov 10 '22 at 12:05

DO $$
DECLARE
    sql text;
    cur CONSTANT refcursor := 'cur';
BEGIN
    sql =  (SELECT format('SELECT %s FROM %I.%I', string_agg(quote_ident(c.column_name), ', '), c.table_schema, c.table_name)
            FROM information_schema."columns" c
            WHERE 
                c.table_schema = 'public' 
                AND c.table_name = 'artikel'
                AND c.data_type = 'integer'
            GROUP BY c.table_schema, c.table_name);
RAISE NOTICE '%', sql; -- for debugging
OPEN cur FOR EXECUTE sql;
END;
$$;
FETCH ALL FROM cur;

score -1 · Answer 2 · answered Oct 29 '22 at 18:49

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:

column
select id, bookcode, maxcode from public.books

After then execute this using EXECUTE command:

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

How to extract columns by data type?

2 Answers2

Linked