Re: Proposal: SELECT * EXCLUDE (...) command - Mailing list pgsql-hackers

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.