INSERT ... ON CONFLICT doesn't work

From: "Jenda Krynicky" <Jenda(at)Krynicky(dot)cz>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: INSERT ... ON CONFLICT doesn't work
Date: 2021-12-01 19:20:56
Message-ID: 61A7CB18.20091.3401B246@Jenda.Krynicky.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

So let's suppose I have a table like this:

CREATE TABLE IF NOT EXISTS public."Lockers"
(
"Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
"Uuid" text COLLATE pg_catalog."default",
"IpAddress" text COLLATE pg_catalog."default",
"State" integer NOT NULL,
"DoorColumns" bytea,
"ConnectionStatus" integer NOT NULL,
"LastConnected" timestamp without time zone,
"LastReportId" integer,
"LongOpenedDoors" bit varying,
"Created" timestamp without time zone,
"Updated" timestamp without time zone,
CONSTRAINT "PK_Lockers" PRIMARY KEY ("Id")
)

CREATE UNIQUE INDEX IF NOT EXISTS "IX_Lockers_Uuid"
ON public."Lockers" USING btree
("Uuid" COLLATE pg_catalog."default" ASC NULLS LAST)
TABLESPACE pg_default;

and a function like this:

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)
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000

AS $BODY$

declare
updated numeric;
current timestamp;
begin
current := timezone('utc', now());

update "Lockers"
set "ConnectionStatus" = 0/*connected*/,
"LastConnected" = current,
"IpAddress" = COALESCE(ipAddress, "Lockers"."IpAddress"),
"Updated" = current
where "Lockers"."Uuid" = lockerUuid;
GET DIAGNOSTICS updated = ROW_COUNT;

IF (updated = 0) then
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;

return Query (
SELECT
"Lockers"."Id",
"Lockers"."Created",
"Lockers"."Uuid",
"Lockers"."State",
"Lockers"."ConnectionStatus",
"Lockers"."LastConnected",
"Lockers"."DoorColumns",
"Lockers"."IpAddress",
"LockerReports"."Created" as "LastReportCreated"
FROM "Lockers"
LEFT JOIN "LockerReports" ON "LockerReports"."Id" =
"Lockers"."LastReportId"
WHERE "Lockers"."Uuid" = lockerUuid);
end;
$BODY$;

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"!

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!

If I try to include the table name as ON CONFLICT (Lockers.Uuid) or
ON CONFLICT ("Lockers"."Uuid"), I get a syntax error.

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")

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

Jenda

=========== Jenda(at)Krynicky(dot)cz == http://Jenda.Krynicky.cz ==========
There is a reason for living. There must be. I've seen it somewhere.
It's just that in the mess on my table ... and in my brain
I can't find it.
--- me

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2021-12-01 19:29:33 Re: case insensitive collation of Greek's sigma
Previous Message Tom Lane 2021-12-01 18:51:38 Re: Pgcrypto extension - decrypt(encrypt(... not returning original data?