Thread

1. Perhaps a possible new feature to a future PostgreSQL release

   Erki Eessaar <erki.eessaar@taltech.ee> — 2023-11-20T09:52:28Z

   Hello
   
   Let me assume that there is a table T with columns a, b, c, d, e, f, g, and h.
   
   If one wants to select data from all the columns except d and e, then one has to write
   
   SELECT a, b, c, f, g, h
   FROM T;
   
   instead of writing
   
   SELECT ALL BUT (d, e)
   FROM T;
   
   or something similar (perhaps by using keywords EXCEPT or EXCLUDE).
   
   The more a table has columns, the more one has to write the column names.
   
   There are systems that support this kind of shorthand syntax in SQL:
   
   BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select-modifiers<https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select-modifiers>
   
   Databricks: https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select.html#syntax
   
   DuckDB: https://duckdb.org/docs/sql/query_syntax/select
   
   Snowflake:https://stephenallwright.com/select-columns-except-snowflake/
   
   I think that such syntax would be useful and if more and more DBMS-s start to offer it, then perhaps one day it will be in the SQL standard as well.
   
   What do you think, is it something that could be added to PostgreSQL?
   
   People are interested of this feature. The following links are just some examples:
   http://www.postgresonline.com/journal/archives/41-How-to-SELECT-ALL-EXCEPT-some-columns-in-a-table.html
   
   https://stackoverflow.com/questions/729197/exclude-a-column-using-select-except-columna-from-tablea
   
   https://dba.stackexchange.com/questions/1957/sql-select-all-columns-except-some
   
   https://www.reddit.com/r/SQL/comments/15x97kw/sql_is_there_a_way_to_just_exclude_1_column_in/
   
   
   Best regards
   Erki Eessaar
   
   
   

2. Re: Perhaps a possible new feature to a future PostgreSQL release

   Laurenz Albe <laurenz.albe@cybertec.at> — 2023-11-20T10:18:06Z

   On Mon, 2023-11-20 at 09:52 +0000, Erki Eessaar wrote:
   > Let me assume that there is a table T with columns a, b, c, d, e, f, g, and h.
   > 
   > If one wants to select data from all the columns except d and e, then one has to write
   > 
   > SELECT a, b, c, f, g, h
   > FROM T;
   > 
   > instead of writing 
   > 
   > SELECT ALL BUT (d, e)
   > FROM T;
   > 
   > or something similar (perhaps by using keywords EXCEPT or EXCLUDE).
   
   This has been discussed before (repeatedly); see for example
   https://www.postgresql.org/message-id/flat/CANcm6wbR3EG7t-G%3DTxy64Yt8nR6YbpzFRuTewJQ%2BkCq%3DrZ8M2A%40mail.gmail.com
   
   All previous attempts went nowhere.
   
   
   > I think that such syntax would be useful and if more and more DBMS-s start to
   > offer it, then perhaps one day it will be in the SQL standard as well.
   
   One of the reasons *against* the feature is that the SQL standard committee
   might one day come up with a feature like that using a syntax that conflicts
   with whatever we introduced on our own.
   
   Yours,
   Laurenz Albe