Viswamitra's blog

This is one of the requirement we get in DBA world where we get all the columns of a table except one or two columns. There is no sql standard which can directly give this information with a query like “select * from table (except some columns)”. If we have large table with more columns, it is difficult to write complete query with all columns except some columns.

We can use the below query to build the required query

select 'select '||string_agg(attname, ',')||' from public.test;' as query from pg_attribute join pg_class on (pg_class.oid=pg_attribute.attrelid) join pg_namespace on (pg_class.relnamespace=pg_namespace.oid) where pg_class.relname='<tablename>' and nspname='<schemaname>' and attnum > 0 and attname not in ('<exception column list>');

Example

I have a table pgbench_accounts table in d_schema and I want to exclude two columns abalance , filler and get remaining.

compliance=# \d d_schema.pgbench_accounts
  Table "d_schema.pgbench_accounts"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 aid      | integer       | not null
 bid      | integer       |
 abalance | integer       |
 filler   | character(84) |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

compliance=#
compliance=#
compliance=# select 'select '||string_agg(attname, ',')||' from public.test;' as query from pg_attribute join pg_class on (pg_class.oid=pg_attribute.attrelid) join pg_namespace on (pg_class.relnamespace=pg_namespace.oid) where pg_class.relname='pgbench_accounts' and nspname='d_schema' and attnum > 0 and attname not in ('abalance','filler');
              query
----------------------------------
 select aid,bid from public.test;
(1 row)