Re: INSERT ... ON CONFLICT doesn't work - Mailing list pgsql-general
| From | David G. Johnston |
|---|---|
| Subject | Re: INSERT ... ON CONFLICT doesn't work |
| Date | |
| Msg-id | CAKFQuwa1+rKkPWxJU=SUsVThUG3GEt6PyKMiU9OL0QOYm1-Yrw@mail.gmail.com Whole thread Raw |
| In response to | INSERT ... ON CONFLICT doesn't work ("Jenda Krynicky" <Jenda@Krynicky.cz>) |
| Responses |
Re: INSERT ... ON CONFLICT doesn't work
("Jenda Krynicky" <Jenda@Krynicky.cz>)
Re: INSERT ... ON CONFLICT doesn't work (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-general |
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.
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.
pgsql-general by date: