Re: [BUG] ON CONFLICT DO UPDATE SET x = EXCLUDED.<virtual-generated-column> errors or silently writes NULL

On Fri, 11 Sept 2026 at 08:45, Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com> wrote:
>
> On Fri, 11 Sept 2026 at 09:30, Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:
> >
> > Please look at a case broken by 783425175:
> > CREATE TABLE t (id int PRIMARY KEY, c int GENERATED ALWAYS AS (tableoid));
> > INSERT INTO t VALUES (1) ON CONFLICT (id) DO UPDATE SET id = 1 WHERE excluded.c > 0;
> >
> > ERROR: XX000: variable not found in subplan target lists
> > LOCATION: fix_join_expr_mutator, setrefs.c:3229
>
> ISTM virtual generated column is expanded to the tableoid reference, but the
> EXCLUDED target list has no tableoid entry for setrefs.c to resolve.
>
> Attached patch adds that entry and the statement you sent works fine with it.
>

Hmm, I'm not sure that this is the right approach. For example,
consider this case, without a generated column:

CREATE TABLE t (id int PRIMARY KEY, val int);
INSERT INTO t VALUES (1,2)
ON CONFLICT (id) DO UPDATE SET val = 0 WHERE excluded.tableoid > 0;

ERROR: column excluded.tableoid does not exist

That error is on point -- "excluded" is not a real table, it is a
pseudo-table constructed from the values proposed for insertion, not
the existing values. As such, it has no system columns.

So, ISTM that when "c" is a virtual generated column, and "excluded.c"
is expanded to "excluded.tableoid", it *should* produce an error.

So rather than trying to fix this, I think it would be better to have
it produce a more meaningful error message.

Regards,
Dean