Question 113994 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to extract columns by data type?

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
Tags:

2

Answers


  1. 0

    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';
    Login or Signup to reply.
  2. 0 
    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search