INSERT ... ON CONFLICT doesn't work
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
Re: INSERT ... ON CONFLICT doesn't work
From: "David G. Johnston"
> On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky 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 makes a 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
= je...@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
Re: INSERT ... ON CONFLICT doesn't work
From: Adrian Klaver
> On 12/1/21 11:20 AM, Jenda Krynicky wrote:
> > So let's suppose I have a table like this:
> >
>
> >
> > 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".
>
> The basic issue is described here:
>
> https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST
>
> "Since the names of variables are syntactically no different from the
> names of table columns, there can be ambiguity in statements that also
> refer to tables: is a given name meant to refer to a table column, or a
> variable? Let's change the previous example to ..."
Looks like a bad design.
>
> ERROR: column reference "Uuid" is ambiguous
> LINE 3: ON CONFLICT ("Uuid")
>
> Is occurring because there is ambiguity between:
>
> "Uuid" text
>
> in RETURNS TABLE and
>
> "Lockers"."Uuid"
While the ON CONFLICT () very explicitely insists on there being a
name of a column of the table being inserted into. Makes nonsense.
> I would say the easiest way out of this is to change:
>
> "Uuid" text --> "uuid_out" text
That would require changes to the application that consumes this
data.
A colleague found a better solution in the meantime. To add
#variable_conflict use_column
right above the DECLARE
Thanks for your time anyway, Jenda
= je...@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
Re: INSERT ... ON CONFLICT doesn't work
From: Tom Lane
> "David G. Johnston" writes:
> > On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky wrote:
> >> 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.
>
> The variable_conflict options that Adrian pointed to are one way out.
>
> It's also possible to qualify the name in the ON CONFLICT clause,
> although I think you have to parenthesize it to do so:
>
> ... ON CONFLICT (("Lockers"."Uuid"))
>
> regards, tom lane
>
ERROR: invalid reference to FROM-clause entry for table "Lockers"
LINE 3: ON CONFLICT (("Lockers"."Uuid"))
^
HINT: There is an entry for table "Lockers", but it cannot be
referenced from this part of the query.
= je...@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
Re: INSERT ... ON CONFLICT doesn't work
From: "David G. Johnston" > On Wed, Dec 1, 2021 at 12:44 PM Jenda Krynicky wrote: > > > 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. > > > > The columns of the returns table are provided to the function as variables > so that one can write: > > output_column1 := 'value'; > output_column2 := 'value'; > return; > > Instead of having to do: > return (output_column1, output_column2); Yeah ... after I specified that instead of a few scalars I intend to return a resultset/recordset/table/whatever-you-want-to-call-it and while using a language that cannot distinguish between columns and variables even at a place that doesn't accept anything other than a column name. Right. > > Right. Because lowercasing everything I write and then comparing it case > > sensitively to the names of database objects makes a lot of sense. I mean > > who would want to use capital letters in names of objects in the first > > place? > > > > Fair point, but you're not going to get much sympathy for not knowing the > rules of the tool that you are using and the choices you've made regarding > them. I agree that your quoting everything has merit, but don't go > complaining that when you forgot the quotes the system tells you the name > is no longer found. I did not create the table and I did not forget the quotes. I removed them in one of many attempts to appease PostgreSQL. I've already learned about those braindead rules. Jenda = je...@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
