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
=========== je...@krynicky.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
