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] 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] 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] https://www.postgresql.org/message-id/flat/CAJTYsWVDs7vkEN-eD1NAnZ7XqcfpXu3tP1wMGzJQ12G6eo1oRw%40mail.gmail.com

Regards,
Kacper Kuras