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@postgresql.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