Thread: Appetite for `SELECT ... EXCLUDE`?
Both DuckDB and Snowflake, as of recently, support a nonstandard `EXCLUDE` clause in the SELECT list to allow excluding fields from a wildcard [0] [1]. Example from the DuckDB announcement [2]: SELECT * EXCLUDE (jar_jar_binks, midichlorians) FROM star_wars Is there any appetite for adding this feature to PostgreSQL? It's quite a bit less typing when you're querying a relation with many columns and want to exclude only a few of those columns. Of course the downside is that it is not (AFAIK) in the SQL standard. I searched the archives and there are a few users asking about such a feature over the years. To be fully transparent, I'm asking in part on behalf of Materialize [3], where we try to follow PostgreSQL's syntax and semantics as closely as possible in our own SQL dialect. (We're happy to carry around extensions that PostgreSQL doesn't have, but our worst case scenario is that we eagerly implement an extension that PostgreSQL implements later in an incompatible way.) [0]: https://github.com/duckdb/duckdb/issues/2199 [1]: https://docs.snowflake.com/en/release-notes/2022-11.html#select-excluding-and-renaming-specific-columns [2]: https://duckdb.org/2022/05/04/friendlier-sql.html [3]: https://materialize.com
Nikhil Benesch <nikhil.benesch@gmail.com> writes:
> Both DuckDB and Snowflake, as of recently, support a nonstandard `EXCLUDE`
> clause in the SELECT list to allow excluding fields from a wildcard [0] [1].
> Example from the DuckDB announcement [2]:
> SELECT * EXCLUDE (jar_jar_binks, midichlorians) FROM star_wars
> Is there any appetite for adding this feature to PostgreSQL?
This has been discussed before and not gone anywhere, e.g. [1] [2].
I think there have been more threads but that was all I found in a
quick archive search. Anyway, as those threads illustrate, there is a
lot of room for variation in how you spell it, where you can write it,
and so on. My own inclination is to not do anything here until/unless
the SQL committee standardizes something, because there's too much
risk of finding ourselves incompatible with the standard.
regards, tom lane
[1] https://www.postgresql.org/message-id/flat/d51371a2-f221-1cf3-4a7d-b2242d4dafdb%40gmail.com
[2]
https://www.postgresql.org/message-id/flat/CANcm6wbR3EG7t-G%3DTxy64Yt8nR6YbpzFRuTewJQ%2BkCq%3DrZ8M2A%40mail.gmail.com
Thanks for the pointers, Tom. Sorry my search didn't turn those up. On Fri, Nov 18, 2022 at 3:18 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Nikhil Benesch <nikhil.benesch@gmail.com> writes: > > Both DuckDB and Snowflake, as of recently, support a nonstandard `EXCLUDE` > > clause in the SELECT list to allow excluding fields from a wildcard [0] [1]. > > > Example from the DuckDB announcement [2]: > > > SELECT * EXCLUDE (jar_jar_binks, midichlorians) FROM star_wars > > > Is there any appetite for adding this feature to PostgreSQL? > > This has been discussed before and not gone anywhere, e.g. [1] [2]. > I think there have been more threads but that was all I found in a > quick archive search. Anyway, as those threads illustrate, there is a > lot of room for variation in how you spell it, where you can write it, > and so on. My own inclination is to not do anything here until/unless > the SQL committee standardizes something, because there's too much > risk of finding ourselves incompatible with the standard. > > regards, tom lane > > [1] https://www.postgresql.org/message-id/flat/d51371a2-f221-1cf3-4a7d-b2242d4dafdb%40gmail.com > [2] https://www.postgresql.org/message-id/flat/CANcm6wbR3EG7t-G%3DTxy64Yt8nR6YbpzFRuTewJQ%2BkCq%3DrZ8M2A%40mail.gmail.com