Re: INSERT ... ON CONFLICT doesn't work - Mailing list pgsql-general

From:    "David G. Johnston" <david.g.johnston@gmail.com>
> On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky <Jenda@krynicky.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
makesa 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@Krynicky.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