Re: INSERT ... ON CONFLICT doesn't work

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> On 12/1/21 11:20 AM, Jenda Krynicky wrote:
> > So let's suppose I have a table like this:
> >
>
> >
> > So pretty please with a cherry on top, how do I explain to postgres
> > 13.4, that yes indeed by "Uuid" I mean the stinking column "Uuid".
>
> The basic issue is described here:
>
> https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST
>
> "Since the names of variables are syntactically no different from the
> names of table columns, there can be ambiguity in statements that also
> refer to tables: is a given name meant to refer to a table column, or a
> variable? Let's change the previous example to ..."

Looks like a bad design.

>
> ERROR: column reference "Uuid" is ambiguous
> LINE 3: ON CONFLICT ("Uuid")
>
> Is occurring because there is ambiguity between:
>
> "Uuid" text
>
> in RETURNS TABLE and
>
> "Lockers"."Uuid"

While the ON CONFLICT () very explicitely insists on there being a
name of a column of the table being inserted into. Makes nonsense.

> I would say the easiest way out of this is to change:
>
> "Uuid" text --> "uuid_out" text

That would require changes to the application that consumes this
data.

A colleague found a better solution in the meantime. To add

#variable_conflict use_column

right above the DECLARE

Thanks for your time anyway, Jenda
===== Jenda(at)Krynicky(dot)cz === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery