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

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:
>
>> insert into t (a, b)
>> values (
>> 1,
>> 1
>> )
>> on conflict (a)
>> where t.b is null -- Error here
>> do update
>> set
>> c = 1
>> returning t.a, t.b, t.c;

>> There's an error reported:
>>
>> 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.
>> Position: 71
>>
>> 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:
>
> ...
> on conflict (a)
> where b is null
> do update
> ...

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.
--
Vik Fearing