insert into insertconflicttest values (6, 'Passionfruit') on conflict (lower(fruit)) do update set fruit = excluded.fruit;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

-- Check the target relation can be aliased

insert into insertconflicttest values (6, 'Passionfruits') on conflict (key) do update set fruit = excluded.fruit;

insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = excluded.fruit; -- ok, no reference to target table

insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = ict.fruit; -- ok, alias

insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = insertconflicttest.fruit; -- error, references aliased away name

insert into insertconflicttestvalues (6,'Passionfruit')on conflict (lower(fruit)) doupdateset fruit=excluded.fruit;

insert into insertconflicttestvalues (6,'Passionfruits')on conflict (key) doupdateset fruit=excluded.fruit;

insert into insertconflicttestAS ictvalues (6,'Passionfruit')on conflict (key) doupdateset fruit=excluded.fruit;-- ok, no reference to target table

insert into insertconflicttestAS ictvalues (6,'Passionfruit')on conflict (key) doupdateset fruit=ict.fruit;-- ok, alias

insert into insertconflicttestAS ictvalues (6,'Passionfruit')on conflict (key) doupdateset fruit=insertconflicttest.fruit;-- error, references aliased away name