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

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.

> 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.

> If I try to include the table name as ON CONFLICT (Lockers.Uuid) or

Even if you didn't get a syntax error that isn't the name of your column...

> ON CONFLICT ("Lockers"."Uuid"), I get a syntax error.
>

This is the one that should work so if its giving a syntax error it is the
one worth questioning.

> If I specify it as
> ON CONFLICT ("Lockers.Uuid")
> I get quite understandably
> ERROR: column "Lockers.Uuid" does not exist
> LINE 3: ON CONFLICT ("Lockers.Uuid")
>
>
Yes, the double quotes in the earlier "Perhaps you meant" error message are
a readability thing, not a "write this instead" thing.

David J.