Date: Thu, 15 Jan 2026 14:15:30 +0100
From: Christoph Berg <myon@debian.org>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Hunaid Sohail <hunaidpgml@gmail.com>, pgsql-hackers@postgresql.org
Subject: Re: Proposal: SELECT * EXCLUDE (...) command
Message-ID: <aWjocpWrkFBnzUuc@msg.df7cb.de>
Mail-Followup-To: Christoph Berg <myon@debian.org>,
	Peter Eisentraut <peter@eisentraut.org>,
	Hunaid Sohail <hunaidpgml@gmail.com>, pgsql-hackers@postgresql.org
References: <CAMWA6yb9GGiJbe9iU88yjurVQCsFgZvW7xSxa0aFLC0Wtz4Aqg@mail.gmail.com>
 <63e1587b-4258-41de-b823-948f8cc692d9@eisentraut.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <63e1587b-4258-41de-b823-948f8cc692d9@eisentraut.org>
Archived-At: <https://www.postgresql.org/message-id/aWjocpWrkFBnzUuc%40msg.df7cb.de>
Precedence: bulk

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