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

From: "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
> On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky <Jenda(at)krynicky(dot)cz> wrote:
>
> >
> > CREATE OR REPLACE FUNCTION public.findorcreatelocker(
> > lockeruuid text,
> > ipaddress text)
> > RETURNS TABLE("Id" integer, "Created" timestamp without time
> > zone, "Uuid" text, "State" integer, "ConnectionStatus" integer,
> > "LastConnected" timestamp without time zone, "DoorColumns" bytea,
> > "IpAddress" text, "LastReportCreated" timestamp without time zone)
> >
> >
>
> > INSERT INTO "Lockers" ("Uuid", "IpAddress", "State",
> > "DoorColumns", "ConnectionStatus", "LastConnected", "LastReportId",
> > "LongOpenedDoors", "Created", "Updated")
> > VALUES (lockerUuid, ipAddress, 0/*new*/, null,
> > 0/*connected*/, current, null, null, current, current)
> > ON CONFLICT ("Uuid")
> > DO NOTHING;
> > end if;
> >
> > How the (censored) am I supposed to write the ON CONFLICT () clause
> > so that it works?
> >
> > Like this it reports:
> >
> > ERROR: column reference "Uuid" is ambiguous
> > LINE 3: ON CONFLICT ("Uuid")
> > ^
> > DETAIL: It could refer to either a PL/pgSQL variable or a table
> > column.
> >
> >
> > THERE IS NO (CENSORED) VARIABLE "Uuid"!
> >
>
> Yes, there is. RETURNS TABLE (... "Uuid" text ... )
>
> Changing that to something else should remove the ambiguity. I agree it is
> not an ideal solution though. I'm not sure what other options exist though.

How's that a variable for gawd's sake? It's a column name too! A
column name in the definition of the resulting table.

> > If I drop the quotes and use just
> > ON CONFLICT (Uuid)
> > I get
> >
> > ERROR: column "uuid" does not exist
> > LINE 3: ON CONFLICT (Uuid)
> > ^
> > HINT: Perhaps you meant to reference the column "Lockers.Uuid".
> >
> > Yes, thank you, that's exactly what I meant. That's what I wrote too,
> > you (censored). I didn't write uuid, I wrote Uuid, you imbecile!
> >
>
> This one is on you for removing the double quotes that your choice of
> identifier names forces you to basically put everywhere.

Right. Because lowercasing everything I write and then comparing it case sensitively to the names of database objects makes a lot of sense. I mean who would want to use capital letters in names of objects in the first place?

Oh, PostgreSQL, how I hate thee, let me count the ways.

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