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

Hello,

On Sat, Oct 10, 2026 at 12:14 AM Kacper Kuras <kacperkuras(at)hotmail(dot)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