Thread: 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

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

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 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
makesa 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

"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

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

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