Re: BUG #16958: "Invalid reference to FROM-clause entry for table" when qualifying columns in "on conflict .. where"

Vik Fearing <vik(at)postgresfriends(dot)org> writes:
> On 4/10/21 9:57 AM, Pantelis Theodosiou wrote:
>> On Fri, Apr 9, 2021 at 11:00 PM PG Bug reporting form <
>> noreply(at)postgresql(dot)org> wrote:
>>> SQL Error [42P01]: ERROR: invalid reference to FROM-clause entry for table "t"
>>> Hint: There is an entry for table "t", but it cannot be referenced from
>>> this part of the query.
>>>
>>> Notice the qualification of the t.b column in the "on conflict .. where"
>>> clause. I don't understand why b cannot be qualified at this location. It
>>> can be qualified in the index definition, looks like a bug to me.

>> You don't need and shouldn't prefix the column with the table name in that
>> part. This should work:

> Not needing to and not being able to are two completely different
> things. I believe tablename qualification should be allowed here even
> if it isn't necessary.

It does seem like a pointless prohibition, but the comment about it in
the source code implies it was intentional. Peter, do you remember
why?

Anyway, if the prohibition isn't necessary, the attached one-liner
gets most of the way to removing it. I'm not quite satisfied with
this though:

regression=# create table t(a int primary key, b int, c int);
CREATE TABLE
regression=# insert into t (a,b) values(1,1) on conflict(a)
where excluded.b is null do update set c=excluded.c+1;
ERROR: missing FROM-clause entry for table "excluded"
LINE 2: where excluded.b is null do update set c=excluded.c+1;
^

Seems like we want the same cannot-be-referenced HINT here as in
the complaint, so just nuking the whole namespace as the code
currently does ought to be revisited.

regards, tom lane