Обсуждение: Proposal: SELECT * EXCLUDE (...) command
Hi,
I would like feedback on the proposed feature: SELECT * EXCLUDE.
The idea is to allow excluding a small number of columns from * without
having to list all remaining columns explicitly. This is mainly intended for
wide tables, columns with metadata, long text fields, or other columns that
may not be needed, making SELECT * more practical while omitting unnecessary data.
Similar functionality exists in other databases such as DuckDB, Snowflake,
and BigQuery. I haven't tried BigQuery and Snowflake seems to only support
unqualified column names as mentioned in its documentation [1]. My implementation
is more like DuckDB, which supports both qualified or unqualified column names.
Qualified stars are supported, and excluded column names may be qualified or
unqualified. If an excluded name matches multiple columns from the * expansion,
all matching columns are excluded. If no column matches, an error is raised.
EXCLUDE is only allowed with *. Using it on non-star expressions results in error.
I have attached an initial draft patch that includes code changes, documentation
updates, and a fairly good amount of test cases. The tests are meant to clearly
describe the intended behavior and scope, including qualified and unqualified
column names, table aliases, joins, multiple tables, and expected error cases.
The patch also allows EXCLUDE to be used with SELECT INTO,
INSERT INTO ... SELECT ..., and RETURNING clauses, similar to how DuckDB
supports it, but the details of those cases can be discussed later.
A few examples:
SELECT * EXCLUDE (email, created_at) FROM users;
SELECT * EXCLUDE (does_not_exist) FROM users; -- error
SELECT * EXCLUDE (users.created_at)
FROM users
JOIN orders ON orders.user_id = users.id;
SELECT * EXCLUDE (users.created_at, orders.amount)
FROM users
JOIN orders ON orders.user_id = users.id;
SELECT users.* EXCLUDE (email), orders.* EXCLUDE (user_id)
FROM users
JOIN orders ON orders.user_id = users.id;
Regards,
Hunaid Sohail
Вложения
On Thu, Jan 8, 2026 at 5:17 PM Hunaid Sohail <hunaidpgml@gmail.com> wrote: > > Hi, > > I would like feedback on the proposed feature: SELECT * EXCLUDE. This has been proposed before. You can see one such discussion here: https://www.postgresql.org/message-id/flat/CANcm6wbR3EG7t-G=Txy64Yt8nR6YbpzFRuTewJQ+kCq=rZ8M2A@mail.gmail.com -- John Naylor Amazon Web Services
On Thu, Jan 8, 2026 at 3:27 PM John Naylor <johncnaylorls@gmail.com> wrote:
This has been proposed before. You can see one such discussion here:
https://www.postgresql.org/message-id/flat/CANcm6wbR3EG7t-G=Txy64Yt8nR6YbpzFRuTewJQ+kCq=rZ8M2A@mail.gmail.com
Thank you for the link.
This looks like an older discussion focused mainly on syntax, scope, and keyword concerns. There was some support for the feature, but no implementation.
Regards,
Hunaid Sohail
Hunaid Sohail <hunaidpgml@gmail.com> writes: > On Thu, Jan 8, 2026 at 3:27 PM John Naylor <johncnaylorls@gmail.com> wrote: >> This has been proposed before. You can see one such discussion here: >> https://www.postgresql.org/message-id/flat/CANcm6wbR3EG7t-G=Txy64Yt8nR6YbpzFRuTewJQ+kCq=rZ8M2A@mail.gmail.com > This looks like an older discussion focused mainly on syntax, scope, and > keyword concerns. There was some support for the feature, but no > implementation. One thing that I think has changed since that 2011 thread is that we've become far more aware that the SQL standard is a moving target. I would now resist adding something like this on the grounds that it's too likely to conflict with something the standards committee does down the road. They might standardize the same functionality with a different syntax, or they could invent some syntax involving EXCLUDING that conflicts enough that we can't support it alongside this. This has already happened to us --- the business with how named arguments for functions are written caused a lot of pain both for us and for our users. Admittedly, the fact that we now have a couple of project members who sit on that committee makes it less likely that we'd get blindsided in the same way again. But that doesn't mean I'm eager to get out front of the standard in places where this sort of conflict is foreseeable. In short: I'd recommend going and seeing if you can sell this idea to the SQL committee. We'd be much more likely to accept an implementation if the syntax got accepted by them. regards, tom lane