Proposal: SELECT * EXCLUDE (...) command

Started by Hunaid Sohail11 months ago21 messageshackers
Jump to latest
#1Hunaid Sohail
hunaidpgml@gmail.com

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]https://docs.snowflake.com/en/sql-reference/sql/select. 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;

Looking forward to the feedback. Thanks!

Regards,
Hunaid Sohail

[1]: https://docs.snowflake.com/en/sql-reference/sql/select

Attachments:

v1-0001-Implement-SELECT-EXCLUDE-.-command.patchapplication/x-patch; name=v1-0001-Implement-SELECT-EXCLUDE-.-command.patchDownload+656-17
#2John Naylor
johncnaylorls@gmail.com
In reply to: Hunaid Sohail (#1)
Re: 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:

/messages/by-id/CANcm6wbR3EG7t-G=Txy64Yt8nR6YbpzFRuTewJQ+kCq=rZ8M2A@mail.gmail.com

--
John Naylor
Amazon Web Services

#3Hunaid Sohail
hunaidpgml@gmail.com
In reply to: John Naylor (#2)
Re: Proposal: SELECT * EXCLUDE (...) command

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:

/messages/by-id/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

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Hunaid Sohail (#3)
Re: Proposal: SELECT * EXCLUDE (...) command

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:
/messages/by-id/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

#5Peter Eisentraut
peter@eisentraut.org
In reply to: Hunaid Sohail (#1)
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.

Attachments:

bcn036r1-Select list EXCLUDE.pdfapplication/pdf; name="bcn036r1-Select list EXCLUDE.pdf"Download+4-5
#6Christoph Berg
myon@debian.org
In reply to: Peter Eisentraut (#5)
Re: Proposal: SELECT * EXCLUDE (...) command

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

#7Hunaid Sohail
hunaidpgml@gmail.com
In reply to: Peter Eisentraut (#5)
Re: Proposal: SELECT * EXCLUDE (...) command

Hi,

On Wed, Jan 14, 2026 at 1:28 AM Peter Eisentraut <peter@eisentraut.org>
wrote:

Example 2:

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

Example 3:

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

Example 5:

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

I will handle these cases in my next patch.

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.

While implementing the patch, I thought it could be useful to allow
this when selecting from multiple tables, as EXCLUDE can be
handy in such cases (as in the example Christoph mentioned).
However, since the proposal notes that only 1 of 5 implementations
allows this and the SQL group may not go in this direction, it seems
reasonable to follow the proposal... but perhaps we can also
leave it to implementations to decide?

Example 8:

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

Example 11:

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

Yes, these cases should work. They seem like bugs in my patch, I will fix
them.

Regards,
Hunaid Sohail

#8Hunaid Sohail
hunaidpgml@gmail.com
In reply to: Hunaid Sohail (#7)
Re: Proposal: SELECT * EXCLUDE (...) command

Hi,

PFA v2 of the patch fixing behavior described in
examples 2, 3, 5, 8, and 11 from the proposal paper.

I have also expanded the test cases to cover the
relevant behaviors described in the paper.

The ambiguous column behavior described in example 6
is not changed in this patch, but it can be easily updated
with a simple if condition if required.

Regards,
Hunaid Sohail

Show quoted text

Attachments:

t53048_8
v2-0001-Implement-SELECT-EXCLUDE-.-command.patchapplication/x-patch; name=v2-0001-Implement-SELECT-EXCLUDE-.-command.patchDownload+679-17
#9Robert Treat
rob@xzilla.net
In reply to: Christoph Berg (#6)
Re: Proposal: SELECT * EXCLUDE (...) command

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

#10Kacper Kuras
kacperkuras@hotmail.com
In reply to: Hunaid Sohail (#8)
Re: Proposal: SELECT * EXCLUDE (...) command

Hi,

This thread has been quiet since February, so I wanted to share a few
things that might help move it forward.

First, the standard side. Peter's report from the June WG3 meeting in
Stockholm [1]https://peter.eisentraut.org/blog/2026/06/30/waiting-for-sql-202y-stockholm-meeting-report says a consolidated proposal was accepted, and the
syntax is different from what v2 implements:

SELECT * (EXCLUDE (a, b)
REPLACE (c WITH x+y, d WITH z*2)
RENAME (e AS ee, f AS foo))
FROM t ...

SELECT t.* (EXCLUDE ...) FROM t ...

Peter, would you be able to share the accepted paper, like you did
with BCN-036R1? I'm mainly wondering whether the EXCLUDE semantics
from BCN-036R1 carried over unchanged (in particular, that an
ambiguous name is an error, as in example 6), and how REPLACE and
RENAME interact with EXCLUDE, e.g. whether the same column may
appear in more than one of the lists.

Second, I applied v2 to current master (trivial conflicts in gram.y,
parse_target.c and parallel_schedule) and tested it on a cassert
build. The patch's own regression test passes, but I ran into four
problems. Setup:

create table t1 (foo int);
create table t2 (foo int, bar int, baz int);
create schema s1;
create schema s2;
create table s1.t3 (a int, b int);
create table s2.t3 (a int, c int);
insert into t1 values (1);
insert into t2 values (10, 20, 30);

1. Assertion failure on a table with a dropped column:

create table tdrop (a int, b int, c int);
alter table tdrop drop column b;
select * exclude (c) from tdrop;

TRAP: failed Assert("n >= 0 && n < list->length"), File: "pg_list.h"

The new loop in expandNSItemAttrs() calls rt_fetch(nscol->p_varno)
for every column, but for a dropped column the ParseNamespaceColumn
is all zeroes, so this is list_nth(rtable, -1).

2. The exclusion leaks into later star expansions. p_dontexpand is
set on the shared ParseNamespaceItem and never reset, so:

select * exclude (bar), * from t2; -- foo | baz | foo | baz
select * exclude (bar), t2.* from t2; -- foo | baz | foo | baz
select t2.* exclude (bar), row(t2.*) from t2; -- row is (10,30)

3. EXCLUDE after anything other than a plain ColumnRef is silently
ignored, because the a_expr branch in target_el only handles
ColumnRef:

select (t2).* exclude (bar) from t2; -- returns foo, bar, baz
select 1 exclude (bar) from t2; -- accepted, no error

4. Qualified names are compared against the alias of the RTE that
p_varno points to, which is the semantic referent, not the name
visible in the query:

select * exclude (j.foo) from (t1 join t2 using (foo)) as j;
ERROR: column "foo" does not exist
-- even though "select j.foo from ..." works

select * exclude (t1.foo) from (t1 join t2 using (foo)) as j;
-- accepted, even though t1.foo cannot be referenced there

Also, the schema part of a three-part name is ignored, so with two
tables of the same name in different schemas:

select * exclude (s1.t3.a) from s1.t3, s2.t3;
-- returns b | c: "a" is excluded from both tables, although
-- s1.t3.a and s2.t3.a are distinguished in the select list

I think most of this comes from matching names as strings (with
RangeVars from qualified_name_list). BCN-036R1 says the entries in
the exclude list should be resolved the same way as column
references in the select list. So perhaps each entry could be
transformed as an ordinary ColumnRef, and the star expansion could
then skip the columns whose Var matches, without modifying the
namespace item. That would give error positions and correct handling
of aliases and schemas for free.

Two behaviors would follow from that as well, and I think both are
still open:

- An ambiguous name would be an error, as in example 6 of BCN-036R1.
Christoph argued upthread for excluding all matching columns
instead. Hopefully the accepted paper settles this. Robert's
USING (id) variant works either way, since the merged column is
not ambiguous.

- The excluded columns would be marked for SELECT privilege checks,
as Peter suggested, while Christoph argued for the opposite.

One more data point, from the user side. I keep wanting this when an
application inserts rows as values of the table's own row type, while
columns such as a serial id or a created_at with a default should get
their defaults. With the INSERT ... BY NAME v2
patch from the other thread [2]/messages/by-id/CAJTYsWVDs7vkEN-eD1NAnZ7XqcfpXu3tP1wMGzJQ12G6eo1oRw@mail.gmail.com applied on top of this v2, that
works:

PREPARE ins(post[]) AS
INSERT INTO post BY NAME
SELECT * EXCLUDE (id, created_at) FROM unnest($1);

Without BY NAME the columns are matched by position, and without
EXCLUDE the NULLs end up in id and created_at. OVERRIDING USER VALUE
only covers identity columns.

Hunaid, are you planning to post a v3 with the standard syntax? I'd
be glad to test it.

To be transparent: I don't write C day to day, so I did this review
with the help of an AI assistant. I reproduced every case above on a
local build.

[1]: https://peter.eisentraut.org/blog/2026/06/30/waiting-for-sql-202y-stockholm-meeting-report
[2]: /messages/by-id/CAJTYsWVDs7vkEN-eD1NAnZ7XqcfpXu3tP1wMGzJQ12G6eo1oRw@mail.gmail.com

Regards,
Kacper Kuras

#11Hunaid Sohail
hunaidpgml@gmail.com
In reply to: Kacper Kuras (#10)
Re: Proposal: SELECT * EXCLUDE (...) command

Hello,

On Sun, Oct 4, 2026 at 12:54 AM Kacper Kuras <kacperkuras@hotmail.com>
wrote:

I think most of this comes from matching names as strings (with
RangeVars from qualified_name_list). BCN-036R1 says the entries in
the exclude list should be resolved the same way as column
references in the select list. So perhaps each entry could be
transformed as an ordinary ColumnRef, and the star expansion could
then skip the columns whose Var matches, without modifying the
namespace item. That would give error positions and correct handling
of aliases and schemas for free.

Thanks for reviewing the patch and reporting bugs. I already switched to
ColumnRef in the upcoming patch since I also found similar kinds of issues.
These issues would hopefully be fixed in the new version.

Hunaid, are you planning to post a v3 with the standard syntax? I'd
be glad to test it.

Sorry for the delay, I was busy with some other important stuff during
this period. v3 is around the corner that includes support for new
standard syntax with REPLACE and RENAME clauses.

If everything looks good, you might see a new version of patch this week.
Thanks!

Regards,
Hunaid Sohail

#12Robert Treat
rob@xzilla.net
In reply to: Hunaid Sohail (#11)
Re: Proposal: SELECT * EXCLUDE (...) command

On Mon, Oct 5, 2026 at 5:39 AM Hunaid Sohail <hunaidpgml@gmail.com> wrote:

On Sun, Oct 4, 2026 at 12:54 AM Kacper Kuras <kacperkuras@hotmail.com> wrote:

I think most of this comes from matching names as strings (with
RangeVars from qualified_name_list). BCN-036R1 says the entries in
the exclude list should be resolved the same way as column
references in the select list. So perhaps each entry could be
transformed as an ordinary ColumnRef, and the star expansion could
then skip the columns whose Var matches, without modifying the
namespace item. That would give error positions and correct handling
of aliases and schemas for free.

Thanks for reviewing the patch and reporting bugs. I already switched to
ColumnRef in the upcoming patch since I also found similar kinds of issues.
These issues would hopefully be fixed in the new version.

+1 for the review work and the framing here after such a long time away.

One thing that wasn't always clear (and hopefully the spec will answer
this better) is that while select t2.* exclude (bar) from t2; should
be supported, it isn't really clear that select (t2).* exclude (bar)
from t2; shouldn't just throw an error.

Hunaid, are you planning to post a v3 with the standard syntax? I'd
be glad to test it.

Sorry for the delay, I was busy with some other important stuff during
this period. v3 is around the corner that includes support for new
standard syntax with REPLACE and RENAME clauses.

No worries, I think a lot of folks are still focused on v19 related
items. Perhaps related, I suspect this might be easier to get in for
v20 if the patches work incrementally; ie. EXCLUDE first, then worry
about REPLACE and RENAME clauses. Granted that depends on the general
complexity of adding those in.

Robert Treat
https://xzilla.net

#13Hunaid Sohail
hunaidpgml@gmail.com
In reply to: Robert Treat (#12)
Re: Proposal: SELECT * EXCLUDE (...) command

Hello,

PFA v3 of the patch.

A few notes about it:
1. This version follows the new standard syntax as mentioned in Peter's
blog along with REPLACE and RENAME clauses.

2. You might see 'star options' in code and documentation, well
that is what I am calling them collectively. This is of course open
to discussion.

3. A column specified in EXCLUDE cannot also be specified in REPLACE
or RENAME. A column may, however, be specified in both
REPLACE and RENAME.

4. The columns in the REPLACE and RENAME clauses are marked
for SELECT privilege checks, whereas the columns in the
EXCLUDE clause are not. (needs discussion)

5. No ambiguity error is raised, and operation is applied to all matching
columns in case of unqualified column names. (needs discussion)

6. Regression tests are expanded and now are now includes:
- Tests of independent clauses
- Tests of clauses in combinations
- SELECT INTO with star options
- INSERT INTO ... SELECT with star options
- RETURNING with star options is rejected, as it made no sense there.
- Error cases

7. The case: select (t2).* exclude (bar)
It comes as A_Indirection node, maybe we can reject these in gram.y
in the check_indirection function. I have not touched it yet.

Other bugs reported by Kacper are fixed in this version, and also
a few similar cases are added to regression tests as well.

I believe there is still some stuff to refactor but just wanted to show
how things are going, and gather feedback from the community.
Thanks!

Regards,
Hunaid Sohail

Attachments:

t53048_13
v3-0001-Implement-SELECT-EXCLUDE-REPLACE-RENAME-clauses.patchapplication/x-patch; name=v3-0001-Implement-SELECT-EXCLUDE-REPLACE-RENAME-clauses.patchDownload+2176-22
#14Marcos Pegoraro
marcos@f10.com.br
In reply to: Hunaid Sohail (#13)
Re: Proposal: SELECT * EXCLUDE (...) command

Em sex., 9 de out. de 2026 às 11:13, Hunaid Sohail <hunaidpgml@gmail.com>
escreveu:

PFA v3 of the patch.

I didn't find any test with nested EXCLUDE, would be good to see if works
with fields that were already EXCLUDED and NOT MATERIALIZED to test not
inlined CTEs

SELECT * (EXCLUDE (id, created_at)) FROM (SELECT * (EXCLUDE (id)) FROM
users);
WITH CTE AS NOT MATERIALIZED (SELECT * (EXCLUDE (id, created_at)) FROM
users) SELECT * (EXCLUDE (id)) FROM CTE;

regards
Marcos

#15Kacper Kuras
kacperkuras@hotmail.com
In reply to: Hunaid Sohail (#13)
Re: Proposal: SELECT * EXCLUDE (...) command

Hi Hunaid,

Thanks for v3. I applied it to current master without conflicts, and
three of the four problems from my previous mail are fixed: no crash
with dropped columns, no leaking into later stars, and qualified names
now follow the normal alias and schema rules. The remaining one,
(t2).*, is your note 7; more on that below. Peter's examples from
BCN-036R1 behave as expected, except example 6, which is your note 5.

4. The columns in the REPLACE and RENAME clauses are marked
for SELECT privilege checks, whereas the columns in the
EXCLUDE clause are not. (needs discussion)

+1 from me. Excluding a column means "I don't want it", so requiring
a privilege on it seems odd. And in PostgreSQL column names are
visible in pg_attribute anyway, so EXCLUDE doesn't reveal anything
about columns you can't read. See also the RETURNING example below.

5. No ambiguity error is raised, and operation is applied to all matching
columns in case of unqualified column names. (needs discussion)

+1 here too. In my previous mail I only listed this as an open
question, but having thought about it more, I prefer this behavior.
If the accepted version still makes this an error, as BCN-036R1 does,
this would only add behavior where the standard raises an error.
Every unambiguous name behaves the same, and a qualified name is
still available when only one of the columns should go.

I don't think the error buys much. Plain * isn't very robust against
schema changes to begin with: if someone adds a "note" column to b
while a already has one, SELECT * FROM a JOIN b silently starts
returning two "note" columns, which a client reading columns by name
can easily mix up. Anyone using * already lives with that, EXCLUDE or
not. So the error would only guard against a small corner of the
problem, while making Christoph's case from upthread more cumbersome:
with JOIN ... ON t1.id = t2.id, excluding id would need
EXCLUDE (t1.id, t2.id). Robert's USING (id) variant avoids that, but
only for the join column itself. Columns that many tables share, like
created_at and updated_at, are ambiguous in any join: with
orders JOIN users ON orders.user_id = users.id,
EXCLUDE (created_at, updated_at) would need four qualified names.

7. The case: select (t2).* exclude (bar)
It comes as A_Indirection node, maybe we can reject these in gram.y
in the check_indirection function. I have not touched it yet.

This is wider than (t2).*. Star options are accepted anywhere a .* can
appear, but they are only applied in a plain select list. Everywhere
else they are silently ignored:

select (t2).* (exclude (bar)) from t2; -- foo, bar, baz
select row(t2.* (exclude (bar))) from t2; -- (10,20,30)
select to_jsonb(t2.* (exclude (bar))) from t2; -- includes "bar"
select * from t2 where t2.* (exclude (bar)) is not null;
select foo from t2 order by t2.* (exclude (bar));

The to_jsonb() case worries me most: someone writing
to_jsonb(u.* (exclude (password))) gets no error, and the password
ends up in the output.

They all come from the same place: star_options is attached in
indirection_el, but only transformTargetList() looks at it. ROW() goes
through transformExpressionList(), which passes NULL, and inside an
expression t.* becomes a whole-row Var. I think all of these should
raise an error. For (t2).*, BCN-036R1 says the same: an <all fields
reference> doesn't support an exclude list. Supporting some of them,
like to_jsonb(), could be a nice follow-up later, but an error is the
safer first step.

- RETURNING with star options is rejected, as it made no sense there.

I think it makes sense there, and it comes for free. RETURNING goes
through transformTargetList() just like a select list, and the docs
say that "the syntax of the RETURNING list is identical to that of the
output list of SELECT". Since RETURNING is a PostgreSQL extension
anyway, keeping it consistent with SELECT seems natural.

I removed the check from gram.y, and everything I tried just works,
for INSERT, UPDATE, DELETE and MERGE, including REPLACE, RENAME and
the new OLD/NEW:

UPDATE r SET bar = bar + 1 WHERE id = 2
RETURNING old.* (EXCLUDE (secret) RENAME (bar AS old_bar)),
new.* (EXCLUDE (id, foo, secret)
RENAME (bar AS new_bar));

Excluding every column hits the existing "RETURNING must have at
least one column" error, and in select_staroptions only the RETURNING
test changes. It is also a good example for your note 4: a role with
UPDATE but without SELECT on "secret" can run

UPDATE r SET bar = bar + 1 WHERE id = 1
RETURNING * (EXCLUDE (secret));

while plain RETURNING * fails with "permission denied". The check in
gram.y only looked at top-level stars anyway, so RETURNING
row(t2.* (exclude (bar))) got through.

If RETURNING is allowed, one thing needs fixing. old and new share the
same range table index, so this is accepted and excludes secret from
new.*:

UPDATE r SET bar = bar + 1 RETURNING new.* (EXCLUDE (old.secret));

Comparing the p_returning_type of the qualifier's nsitem with the
varreturningtype of the Var should catch it, as in example 10.

Sorry for mixing the threads a bit again, but this shows a nice use
of both features together. The combination with INSERT ... BY NAME [1]/messages/by-id/CAJTYsWVDs7vkEN-eD1NAnZ7XqcfpXu3tP1wMGzJQ12G6eo1oRw@mail.gmail.com
still works with v3, and RENAME makes it even better: an application
type whose field names differ from the table's columns can be mapped
directly.

INSERT INTO post BY NAME
SELECT * (EXCLUDE (id) RENAME (heading AS title, content AS body))
FROM unnest($1::post_dto[]);

Minor, since you mentioned refactoring: a few helpers would remove
most of the repetition, e.g. a makeStarOptions() in gram.y, one shared
rule for the column in exclude_item/replace_item/rename_item, and one
function for the three loops in check_star_options().

The new staroptions argument of ExpandColumnRefStar() is redundant,
because the options are already in the A_Star at the end of
cref->fields. transformExpressionList() passes NULL there even though
cref has options, which is the ROW() case above. If
ExpandColumnRefStar() read them from cref itself, that case could be
caught in one place.

Finally, when no column matches, check_star_options() reports a
missing column without checking the qualifier. For EXCLUDE
(nosuch.zzz) it reports

ERROR: column nosuch.zzz does not exist

where a select list reports

ERROR: missing FROM-clause entry for table "nosuch"

Do you plan to add it to the commitfest? PG20-3 is open for new
entries until the end of October, and cfbot would then also test
every new version on several platforms.

[1]: /messages/by-id/CAJTYsWVDs7vkEN-eD1NAnZ7XqcfpXu3tP1wMGzJQ12G6eo1oRw@mail.gmail.com

Regards,
Kacper Kuras

#16Hunaid Sohail
hunaidpgml@gmail.com
In reply to: Marcos Pegoraro (#14)
Re: Proposal: SELECT * EXCLUDE (...) command

Hello,

I didn't find any test with nested EXCLUDE, would be good to see if works

with fields that were already EXCLUDED and NOT MATERIALIZED to test not
inlined CTEs

SELECT * (EXCLUDE (id, created_at)) FROM (SELECT * (EXCLUDE (id)) FROM
users);
WITH CTE AS NOT MATERIALIZED (SELECT * (EXCLUDE (id, created_at)) FROM
users) SELECT * (EXCLUDE (id)) FROM CTE;

I will add such cases in regression tests.
As of v3, the output of such queries looks like:

-- id already excluded in inner query, email and created_at excluded in
outer query
SELECT * (EXCLUDE (email, created_at)) FROM (SELECT * (EXCLUDE (id)) FROM
users); -- returns name only
name
-------
Alice
Bob
Carol
(3 rows)

-- Trying to exclude id again in outer query (error case)
SELECT * (EXCLUDE (id, created_at)) FROM (SELECT * (EXCLUDE (id)) FROM
users);
ERROR: column "id" does not exist
LINE 1: SELECT * (EXCLUDE (id, created_at)) FROM (SELECT * (EXCLUDE ...
^
-- Trying to exclude id again in outer query (error case)
WITH CTE AS NOT MATERIALIZED (SELECT * (EXCLUDE (id, created_at)) FROM
users)
SELECT * (EXCLUDE (id)) FROM CTE;
ERROR: column "id" does not exist
LINE 2: SELECT * (EXCLUDE (id)) FROM CTE;
^

Regards,
Hunaid Sohail

#17Hunaid Sohail
hunaidpgml@gmail.com
In reply to: Kacper Kuras (#15)
Re: Proposal: SELECT * EXCLUDE (...) command

Hello,

On Sat, Oct 10, 2026 at 12:14 AM Kacper Kuras <kacperkuras@hotmail.com>
wrote:

7. The case: select (t2).* exclude (bar)
It comes as A_Indirection node, maybe we can reject these in gram.y
in the check_indirection function. I have not touched it yet.

This is wider than (t2).*. Star options are accepted anywhere a .* can
appear, but they are only applied in a plain select list. Everywhere
else they are silently ignored:

select (t2).* (exclude (bar)) from t2; -- foo, bar, baz
select row(t2.* (exclude (bar))) from t2; -- (10,20,30)
select to_jsonb(t2.* (exclude (bar))) from t2; -- includes "bar"
select * from t2 where t2.* (exclude (bar)) is not null;
select foo from t2 order by t2.* (exclude (bar));

The to_jsonb() case worries me most: someone writing
to_jsonb(u.* (exclude (password))) gets no error, and the password
ends up in the output.

They all come from the same place: star_options is attached in
indirection_el, but only transformTargetList() looks at it. ROW() goes
through transformExpressionList(), which passes NULL, and inside an
expression t.* becomes a whole-row Var. I think all of these should
raise an error. For (t2).*, BCN-036R1 says the same: an <all fields
reference> doesn't support an exclude list. Supporting some of them,
like to_jsonb(), could be a nice follow-up later, but an error is the
safer first step.

Actually, the issue is that the new standard syntax adds some extra
outer parentheses: SELECT ... ( staroptions )
Like v2, we cannot add a rule to directly to target_el like:
| a_expr star_options
due to gram conflicts. To support qualified stars I had to add star_options
to indirection_el but the issue is that it is also used in other cases,
like you
mentioned above. I do agree that we need to limit scope of star_options
and only use it with target_el. Perhaps, we need to take out '.' '*' from
indirection_el and refactor some of that part to make it work.

- RETURNING with star options is rejected, as it made no sense there.

I think it makes sense there, and it comes for free. RETURNING goes
through transformTargetList() just like a select list, and the docs
say that "the syntax of the RETURNING list is identical to that of the
output list of SELECT". Since RETURNING is a PostgreSQL extension
anyway, keeping it consistent with SELECT seems natural.

I don't have any issue with EXCLUDE clause here, my actual concern
was using REPLACE clause, since RETURNING would return the
affected row after UPDATE/DELETE so replacing them with some other
expression seems odd. But that's just my opinion, we can have some
other opinions on this as well.

Finally, when no column matches, check_star_options() reports a
missing column without checking the qualifier. For EXCLUDE
(nosuch.zzz) it reports

ERROR: column nosuch.zzz does not exist

where a select list reports

ERROR: missing FROM-clause entry for table "nosuch"

Noted.

Do you plan to add it to the commitfest? PG20-3 is open for new
entries until the end of October, and cfbot would then also test
every new version on several platforms.

Done. Thanks for the reminder.
Here is CF entry: https://commitfest.postgresql.org/patch/7437/

Regards,
Hunaid Sohail

#18Kacper Kuras
kacperkuras@hotmail.com
In reply to: Hunaid Sohail (#17)
Re: Proposal: SELECT * EXCLUDE (...) command

Hi Hunaid,

Thanks for adding it to the commitfest. I've added myself as a
reviewer.

I do agree that we need to limit scope of star_options
and only use it with target_el. Perhaps, we need to take out '.' '*' from
indirection_el and refactor some of that part to make it work.

Before going down that road: a few checks in parse analysis might be
simpler and safer. The grammar could stay as it is, with star options
rejected wherever they aren't used. The changes would then stay in a
few functions, while taking '.' '*' out of indirection_el means
reworking how every existing .* is parsed (t.* in expressions,
(expr).*, ROW(t.*), RETURNING t.*, ...). PostgreSQL already rejects
(expr).* inside an expression this way ("row expansion via "*" is not
supported here" in transformIndirection()). As far as I can see, three
checks would cover the cases from my previous mail:

- transformColumnRef(), for t.* inside an expression (function
arguments, WHERE, ORDER BY, ...);
- ExpandColumnRefStar() with make_target_entry = false, for ROW() and
VALUES;
- ExpandIndirectionStar(), for (t2).*.

It would also leave room for supporting some of these cases later.

I don't have any issue with EXCLUDE clause here, my actual concern
was using REPLACE clause, since RETURNING would return the
affected row after UPDATE/DELETE so replacing them with some other
expression seems odd.

I see the point, but RETURNING already accepts arbitrary expressions,
e.g. RETURNING id, lower(email), and REPLACE is just a shorter way to
write that. One practical case is masking:

UPDATE users SET email = lower(email)
RETURNING * (REPLACE (password WITH '***'));

Allowing EXCLUDE but not REPLACE would also need extra code and a note
in the docs, which currently say that the RETURNING list has the same
syntax as the output list of SELECT.

Also, cfbot's "Linux - Autoconf" job fails: that build enables
ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS, which warns that roles
created by regression tests should be named regress_*, and the test
creates "tuser". Renaming it, e.g. to regress_staroptions_user, should
fix it.

Regards,
Kacper Kuras

#19Hunaid Sohail
hunaidpgml@gmail.com
In reply to: Kacper Kuras (#18)
Re: Proposal: SELECT * EXCLUDE (...) command

Hello,

PFA v4 of the patch with following changes:

Staroptions scope is limited now by throwing errors from C
functions (instead of gram.y) if used with expressions or other
clauses like where/order by etc as reported above by Kacper.

Since there were no other opinions (or objections) so
RETURNING is now allowed with staroptions.

Apart from code refactoring, a few more regression tests are
added regarding RETURNING clause and nested queries.

It would be nice to get some kind of update regarding this
feature in SQL Standard to see if we are going in the
right direction.

Regards,
Hunaid Sohail

Attachments:

t53048_19
v4-0001-Implement-SELECT-EXCLUDE-REPLACE-RENAME-clauses.patchapplication/x-patch; name=v4-0001-Implement-SELECT-EXCLUDE-REPLACE-RENAME-clauses.patchDownload+2293-19
#20Kacper Kuras
kacperkuras@hotmail.com
In reply to: Hunaid Sohail (#19)
Re: Proposal: SELECT * EXCLUDE (...) command

Hi Hunaid,

Thanks for v4. I applied it to current master and went through the
cases from my earlier mails again. Everything I raised on v3 is
addressed, the minor points too. In particular, star options are
rejected outside the select list, RETURNING works (including OLD/NEW,
REPLACE and RENAME), new.* (EXCLUDE (old.x)) is rejected, and a bad
qualifier now gets the same error as in a select list. cfbot is green
as well.

One cosmetic point, take it or leave it: applyStarOptions() goes
through the lists in five quite similar loops. A small helper that
returns the matching item from one list and reports duplicates would
cut it down to finding the three items, checking the EXCLUDE
conflicts, and applying REPLACE and RENAME.

From my side the patch looks ready, so I'm marking it Ready for
Committer. The main open question is whether the behavior matches
what was accepted into the standard, e.g. for ambiguous names, and
that needs Peter's input.

Regards,
Kacper Kuras

#21Hunaid Sohail
hunaidpgml@gmail.com
In reply to: Kacper Kuras (#20)