Обсуждение: Proposal: SELECT * EXCLUDE (...) command

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

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

On 08.01.26 11:17, Hunaid Sohail wrote:
> I would like feedback on the proposed feature: SELECT * EXCLUDE.

I proposed this feature to the SQL standard in September 2025.  It was 
generally welcome, but the processing was postponed because some other 
people wanted to add even more clauses for postprocessing asterisk 
expansions, such as REPLACE or RENAME, which are also present in some 
other implementations.  So we'll likely come back in a few months with 
another proposal that includes more of that.  But in the meantime, I 
think implementation work can proceed.

I have attached my change proposal paper here.  This shows the intended 
semantics, and I think those were pretty much agreed upon by the SQL 
working group.

I tested your patch against the examples given in that paper:

Example 2:

=> select * exclude (bar) from t1;
ERROR:  42703: column "bar" does not exist

Raising an error is correct, but you should do some work to get an error 
indicator, like you get when you use a non-existing column in the select 
list:

=> select bar from t1;
ERROR:  42703: column "bar" does not exist
LINE 1: select bar from t1;
                ^

Example 3:

=> select * exclude (foo) from t1;
ERROR:  02000: SELECT list is empty after excluding all columns

This is arguably correct, but I raise in the paper the possibility that 
implementations that support zero-column tables should support this as 
an extension.  I suggest to do this here.

Example 5:

=> select * exclude (bar, bar) from t2;
ERROR:  42703: column "bar" does not exist

It is correct to raise an error for duplicate entries in the exclude 
list, but the specific error raised here doesn't make sense.

Example 6:

=> select * exclude (foo) from t1, t2;
ERROR:  02000: SELECT list is empty after excluding all columns

My paper proposes that this should be an error because foo is ambiguous. 
  This is where DuckDB diverges, and you mentioned that you followed 
DuckDB.  But I think the SQL standard is not going to go that way.  But 
moreover, the error you raise here doesn't make sense.  It looks like 
your implementation is checking the empty select list case on a 
per-table basis, which would be wrong.

Example 8:

=> select * exclude (t1.foo) from t1, t2;
ERROR:  02000: SELECT list is empty after excluding all columns

This is similarly wrong.

Example 11:

=> select foo, t1.* exclude (foo) from t1;
ERROR:  02000: SELECT list is empty after excluding all columns

And this error is also wrong for similar reasons.


You also need to be careful with column privileges.  For example:

create table t5 (a int, b int, c int);
grant select (a) on table t5 to user2;
-- as user2
select * exclude (b, c) from t5;

At least under the SQL standard security model, this should be an error, 
because you need to check the privileges of b and c.  This is because 
you shouldn't be able to use this feature to probe for the existence of 
columns that you otherwise don't have access to.  PostgreSQL doesn't 
quite work that way, but I think for a new feature we should take this 
into account.  (My paper doesn't call this out because it falls out of 
existing rules.)


So it looks like this implementation will need a bit of work, but I 
welcome that this is taken up.  In the meantime, if you or anyone has 
comments on the SQL change proposal, let me know also.  We can develop 
these in parallel.

Re: Peter Eisentraut
> => select * exclude (foo) from t1, t2;
> ERROR:  02000: SELECT list is empty after excluding all columns
> 
> My paper proposes that this should be an error because foo is ambiguous.

Consider this example:

create table t1(id int, data text, more_data text);
create table t2(id int, other_data text, different_data text);

Now if you wanted just the data without the surrogate keys, you would
want to say:

select * exclude (id) from t1 join t2 on t1.id = t2.id;

Having to specify (t1.id, t2.id) would make it cumbersome to use,
especially considering "exclude" would mostly be useful for
interactive use.


> You also need to be careful with column privileges.  For example:
> 
> create table t5 (a int, b int, c int);
> grant select (a) on table t5 to user2;
> -- as user2
> select * exclude (b, c) from t5;
> 
> At least under the SQL standard security model, this should be an error,
> because you need to check the privileges of b and c.  This is because you
> shouldn't be able to use this feature to probe for the existence of columns
> that you otherwise don't have access to.

I would actually argue the exact other way round. If you have access
to a table except some column (like everything in a users table except
for the password), you would want to be able to write

select * exclude (password) from users;

This is a very natural way to use the feature. If referencing
"password" was forbidden, it would exactly defeat the reason for using
EXCLUDE here.

Christoph

On Thu, Jan 15, 2026 at 8:15 AM Christoph Berg <myon@debian.org> wrote:
>
> Re: Peter Eisentraut
> > => select * exclude (foo) from t1, t2;
> > ERROR:  02000: SELECT list is empty after excluding all columns
> >
> > My paper proposes that this should be an error because foo is ambiguous.
>
> Consider this example:
>
> create table t1(id int, data text, more_data text);
> create table t2(id int, other_data text, different_data text);
>
> Now if you wanted just the data without the surrogate keys, you would
> want to say:
>
> select * exclude (id) from t1 join t2 on t1.id = t2.id;
>
> Having to specify (t1.id, t2.id) would make it cumbersome to use,
> especially considering "exclude" would mostly be useful for
> interactive use.
>

A slightly different but perhaps more compelling version of this would be:

select * exclude (id) from t1 join t2 using (id);

Without the exclude, the returned row would only have a single id
column, so it seems pretty natural to similarly add an exclude for
that single id column.

Robert Treat
https://xzilla.net