INSERT ... ON CONFLICT doesn't work

Поиск
Список
Период
Сортировка
От Jenda Krynicky
Тема INSERT ... ON CONFLICT doesn't work
Дата
Msg-id 61A7CB18.20091.3401B246@Jenda.Krynicky.cz
обсуждение исходный текст
Ответы Re: INSERT ... ON CONFLICT doesn't work  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: INSERT ... ON CONFLICT doesn't work  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список 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@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




В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Pgcrypto extension - decrypt(encrypt(... not returning original data?
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: case insensitive collation of Greek's sigma