Re: Proposal: SELECT * EXCLUDE (...) command

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.