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

On Fri, Apr 9, 2021 at 11:00 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16958
> Logged by: Lukas Eder
> Email address: lukas(dot)eder(at)gmail(dot)com
> PostgreSQL version: 13.2
> Operating system: Docker on Windows
> Description:
>
> I'm using Docker on Windows:
>
> -------------------------------------------------------------------
> select version();
> -------------------------------------------------------------------
> PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled
> by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
>
> Consider the following SQL script:
> -------------------------------------------------------------------
> create table t (
> a int,
> b int,
> c int
> );
>
> create unique index i on t(a)
> where t.b is null;
>
> 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;
>
> drop table t;
> -------------------------------------------------------------------
>
> 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
...