1

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' );
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user3673794
  • 11
  • 1
  • Can you provide sample data and desired results? It is not clear what you really want to do. – Gordon Linoff Apr 18 '20 at 19:25
  • 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. – user3673794 Apr 18 '20 at 19:33
  • As far as I know, there is no simple way to do that in Postgres. – Gordon Linoff Apr 18 '20 at 19:44
  • 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? – user3673794 Apr 18 '20 at 19:45
  • It's pretty simple after all ... – Erwin Brandstetter Apr 18 '20 at 20:11
  • @ErwinBrandstetter . . . Dynamic SQL is not a simple solution. BigQuery's `except` syntax is a simple solution. – Gordon Linoff Apr 18 '20 at 21:27
  • @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!) – Erwin Brandstetter Apr 18 '20 at 22:26

1 Answers1

1

Build the DDL command dynamically. You can do it in two steps:

  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. (Check it's good!) Then 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. Related:

But it can be done in a single round trip to the server, too:

With a DO statement from any client

DO is just a simple wrapper for ad-hoc execution of PL/pgSQL code. You might do the same in a function or procedure.

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
$$;

Simpler with psql meta-command \gexec

Since 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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228