Subject: Re: BUG #16958: "Invalid reference to FROM-clause entry for table"
 when qualifying columns in "on conflict .. where"
To: Pantelis Theodosiou <ypercube@gmail.com>,
 Lukas Eder <lukas.eder@gmail.com>, pgsql-bugs@lists.postgresql.org
References: <16958-963f638020de271c@postgresql.org>
 <CAE3TBxxXQ2+7EaBvb+zEFL6iTw90xq0+faHEqK2gDS0tTgiNmQ@mail.gmail.com>
From: Vik Fearing <vik@postgresfriends.org>
Message-ID: <8a396e1e-c2a0-5ba3-180e-697082ae397f@postgresfriends.org>
Date: Sat, 10 Apr 2021 12:28:04 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.7.1
MIME-Version: 1.0
In-Reply-To: 
 <CAE3TBxxXQ2+7EaBvb+zEFL6iTw90xq0+faHEqK2gDS0tTgiNmQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/8a396e1e-c2a0-5ba3-180e-697082ae397f%40postgresfriends.org>
Precedence: bulk

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