From: Vik Fearing <vik@postgresfriends.org>
To: Pantelis Theodosiou <ypercube@gmail.com>
To: Lukas Eder <lukas.eder@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16958: "Invalid reference to FROM-clause entry for table" when qualifying columns in "on conflict .. where"
Date: Sat, 10 Apr 2021 12:28:04 +0200
Message-ID: <8a396e1e-c2a0-5ba3-180e-697082ae397f@postgresfriends.org> (raw)
In-Reply-To: <CAE3TBxxXQ2+7EaBvb+zEFL6iTw90xq0+faHEqK2gDS0tTgiNmQ@mail.gmail.com>
References: <16958-963f638020de271c@postgresql.org>
<CAE3TBxxXQ2+7EaBvb+zEFL6iTw90xq0+faHEqK2gDS0tTgiNmQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: vik@postgresfriends.org, ypercube@gmail.com, lukas.eder@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16958: "Invalid reference to FROM-clause entry for table" when qualifying columns in "on conflict .. where"
In-Reply-To: <8a396e1e-c2a0-5ba3-180e-697082ae397f@postgresfriends.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox