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
=========== Jenda@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
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 ..."
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"
I would say the easiest way out of this is to change:
"Uuid" text --> "uuid_out" text
Jenda
--
Adrian Klaver
adrian.klaver@aklaver.com
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 getERROR: 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.
From: "David G. Johnston" <david.g.johnston@gmail.com>
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.
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 getERROR: 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
===== Jenda@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
From: Adrian Klaver <adrian.klaver@aklaver.com>
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
===== Jenda@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
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky <Jenda@krynicky.cz> 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
On Wed, Dec 1, 2021 at 12:44 PM Jenda Krynicky <Jenda@krynicky.cz> 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);
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.
David J.
On Wed, Dec 1, 2021 at 12:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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"))
This really needs to be confirmed and, if so, better documented on the
INSERT page, since adding a schema to the index_column_name does not
intuitively turn it into an index_expression which is what the syntax
diagram says requires an extra pair of parentheses.
David J.
On Wed, Dec 1, 2021 at 12:59 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Wed, Dec 1, 2021 at 12:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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"))
This really needs to be confirmed and, if so, better documented on the
INSERT page, since adding a schema to the index_column_name does not
intuitively turn it into an index_expression which is what the syntax
diagram says requires an extra pair of parentheses.
That of course should be adding a table qualifier to the column, not a
schema qualifier. I get why INSERT doesn't really care, the source table
is never in question in SQL-land, but as we see here there is a valid need
for it in plpgsql.
David J.
From: Tom Lane <tgl@sss.pgh.pa.us>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky <Jenda@krynicky.cz> 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.
===== Jenda@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
From: "David G. Johnston" <david.g.johnston@gmail.com>
On Wed, Dec 1, 2021 at 12:44 PM Jenda Krynicky <Jenda@krynicky.cz> 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
===== Jenda@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
"Jenda Krynicky" <Jenda@Krynicky.cz> writes:
From: Tom Lane <tgl@sss.pgh.pa.us>
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"))
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.
Hmm, sorry, I'd tested that in HEAD, but it seems not to work in v13.
Looking closer, I think it was a not-mentioned-in-the-commit-log
side-effect of [1]https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=6c0373ab7. Anyway, seems like you need to fall back on
variable_conflict in v13.
regards, tom lane
[1]: https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=6c0373ab7
On Wed, Dec 1, 2021 at 1:04 PM Jenda Krynicky <Jenda@krynicky.cz> wrote:
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.
Nothing is perfect, especially something that started decades ago and has
to be concerned about ongoing compatibility. The best part is you got some
clarification (that the RETURNS column is indeed the main cause of the
issue) quickly from two different people and eventually came across the
#variable_conflict hack that at least here is the least invasive solution
to what is arguably a deficiency in the interaction between SQL and
pl/pgsql. They happen, again nothing is perfect.
David J.
On 12/1/21 11:43, Jenda Krynicky wrote:
From: Adrian Klaver <adrian.klaver@aklaver.com>
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.
House rules.
My experience on this across a variety jobs software and not:
1) Learn the house rules
2) Do not expect them to follow your view of world.
3) Do not be surprised if the house does not follow it's own rules.
While the ON CONFLICT () very explicitely insists on there being a
name of a column of the table being inserted into. Makes nonsense.
No it does not expect this(house rules remember):
https://www.postgresql.org/docs/current/sql-insert.html
"
[ ON CONFLICT [ conflict_target ] conflict_action ]
where conflict_target can be one of:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [
opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
"
And further down:
https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
"conflict_target
Specifies which conflicts ON CONFLICT takes the alternative action
on by choosing arbiter indexes. Either performs unique index inference,
or names a constraint explicitly. For ON CONFLICT DO NOTHING, it is
optional to specify a conflict_target; when omitted, conflicts with all
usable constraints (and unique indexes) are handled. For ON CONFLICT DO
UPDATE, a conflict_target must be provide
...
index_column_name
The name of a table_name column. Used to infer arbiter indexes.
Follows CREATE INDEX format. SELECT privilege on index_column_name is
required.
index_expression
Similar to index_column_name, but used to infer expressions on
table_name columns appearing within index definitions (not simple
columns). Follows CREATE INDEX format. SELECT privilege on any column
appearing within index_expression is required.
collation
When specified, mandates that corresponding index_column_name or
index_expression use a particular collation in order to be matched
during inference. Typically this is omitted, as collations usually do
not affect whether or not a constraint violation occurs. Follows CREATE
INDEX format.
opclass
When specified, mandates that corresponding index_column_name or
index_expression use particular operator class in order to be matched
during inference. Typically this is omitted, as the equality semantics
are often equivalent across a type's operator classes anyway, or because
it's sufficient to trust that the defined unique indexes have the
pertinent definition of equality. Follows CREATE INDEX format.
index_predicate
Used to allow inference of partial unique indexes. Any indexes that
satisfy the predicate (which need not actually be partial indexes) can
be inferred. Follows CREATE INDEX format. SELECT privilege on any column
appearing within index_predicate is required.
constraint_name
Explicitly specifies an arbiter constraint by name, rather than
inferring a constraint or index.
condition
An expression that returns a value of type boolean. Only rows for
which this expression returns true will be updated, although all rows
will be locked when the ON CONFLICT DO UPDATE action is taken. Note that
condition is evaluated last, after a conflict has been identified as a
candidate to update.
"
--
Adrian Klaver
adrian.klaver@aklaver.com
On 12/1/21 12:55, Adrian Klaver wrote:
On 12/1/21 11:43, Jenda Krynicky wrote:
From: Adrian Klaver <adrian.klaver@aklaver.com>
On 12/1/21 11:20 AM, Jenda Krynicky wrote:
While the ON CONFLICT () very explicitely insists on there being a
name of a column of the table being inserted into. Makes nonsense.No it does not expect this(house rules remember):
Better wording would be, it is only one of the things it expects.
--
Adrian Klaver
adrian.klaver@aklaver.com
Perhaps I missed something, but why all this effort to reference the column
and not just reference IX_Lockers_Uuid for the on conflict clause?
On 12/1/21 20:20, Michael Lewis wrote:
Perhaps I missed something, but why all this effort to reference the
column and not just reference IX_Lockers_Uuid for the on conflict clause?
Two reasons:
1) The OP thought that ON CONFLICT could only reference a column.
2) Explaining why the error occurred in the first place, which is down
to variable/column name ambiguity.
--
Adrian Klaver
adrian.klaver@aklaver.com