Filter column names from existing table for SQL DDL statement

Question

Is it possible to filter on column names themselves in psql? I want to generate a limited version of the original table (with several hundred columns) in a separate schema a la (pseudocode):

create table why.am_i_doing_this
    select *
    from original.table 
    where column_name_of_the_table not in ('column_1', 'column_2' );

Avigation

Can you provide sample data and desired results? It is not clear what you really want to do. – Jacquesjacquet

Let's say I have a table schema_1.original_table (A text, B text, C text). I want to run something like create table schema_2.new_table as select * from schema_1.original_table where column_name NOT IN (A, B); I am basically looking to see if there is a way to "deselect" columns I am not interested in from the original table rather than having to put down all of the 300-odd column names I am interested in within a single create table query if that makes sense. – Avigation

As far as I know, there is no simple way to do that in Postgres. – Jacquesjacquet

I figured as much. Been fiddling around with it for the best part of the last hour or two. Do you have any idea what a not so simple approach could look like by any chance? – Avigation

It's pretty simple after all ... – Coelenterate

@ErwinBrandstetter . . . Dynamic SQL is not a simple solution. BigQuery's except syntax is a simple solution. – Jacquesjacquet

@GordonLinoff: True, dynamic SQL in a DO statement is not entirely simple. But that's just the cherry on top. The basic statement is simple enough. And while at the topic of simplicity, I added \gexec ... (BTW, I'd love BigQuery's EXCEPT in standard SQL!) – Coelenterate

Answer 1

Two-step flow

Build the DDL command dynamically and then execute:

1. Build statement

SELECT 'CREATE TABLE why.am_i_doing_this AS SELECT '
    || string_agg(column_name, ', ' ORDER BY ordinal_position)
    || ' FROM original.table'
FROM   information_schema.columns
WHERE  table_schema = 'original'
AND    table_name = 'table'
AND    column_name NOT IN ('column_1', 'column_2');

2. Execute statement

After checking it's good, execute the generated statement in a second round trip to the server.

This is based on the information schema view information_schema.columns. Alternatively, you could use pg_catalog.pg_attribute. See:

• How to check if a table exists in a given schema

DO statement from any client

DO is just a simple wrapper for ad-hoc execution of PL/pgSQL code. Alternatively, persist the code in a function or procedure.

With EXECUTE, you can build and execute dynamic SQL commands in a single round trip to the server:

DO
$do$
BEGIN
   EXECUTE (
   SELECT 'CREATE TABLE why.am_i_doing_this AS SELECT '
       || string_agg(column_name, ', ' ORDER BY ordinal_position)
       || ' FROM original.table'
   FROM   information_schema.columns
   WHERE  table_schema = 'original'
   AND    table_name = 'table'
   AND    column_name NOT IN ('column_1', 'column_2')
   );
END
$do$;

psql meta-command \gexec

You mentioned the default interactive terminal psql. There you can use \gexec. It ...

Sends the current query buffer to the server, then treats each column of each row of the query's output (if any) as a SQL statement to be executed.

So:

SELECT 'CREATE TABLE why.am_i_doing_this AS SELECT '
    || string_agg(column_name, ', ' ORDER BY ordinal_position)
    || ' FROM original.table'
FROM   information_schema.columns
WHERE  table_schema = 'original'
AND    table_name = 'table'
AND    column_name NOT IN ('column_1', 'column_2')\gexec

Coelenterate