Insert, przy duplicate update w PostgreSQL?

Ostrzeżenie: nie jest to bezpieczne, jeśli jest wykonywane z wielu sesji w tym samym czasie (patrz zastrzeżenia poniżej).

Innym sprytnym sposobem na "UPSERT" w postgresql jest wykonanie dwóch sekwencyjnych poleceń UPDATE/INSERT, z których każda ma odnieść sukces lub nie przynieść żadnego efektu.

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
       SELECT 3, 'C', 'Z'
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

Aktualizacja powiedzie się, jeśli wiersz z "id=3" już istnieje, w przeciwnym razie nie ma żadnego efektu.

Wstawianie zakończy się sukcesem tylko wtedy, gdy wiersz z "id = 3" nie istnieje.

Możesz połącz te dwa w jeden ciąg znaków i uruchom je za pomocą pojedynczej instrukcji SQL wykonaj z aplikacji. Prowadzenie ich razem w jednej transakcji jest wysoce zalecane.

Działa to bardzo dobrze, gdy jest uruchamiane w izolacji lub na zablokowanej tabeli, ale podlega Warunkom wyścigu, które oznaczają, że może nadal nie działać z duplikatem błędu klucza, jeśli wiersz jest wstawiany jednocześnie, lub może zakończyć się bez wiersza wstawianego, gdy wiersz jest usuwany jednocześnie. Transakcja SERIALIZABLE Na PostgreSQL Wersja 9.1 lub wyższa poradzi sobie z tym niezawodnie kosztem bardzo wysokiej awaryjności serializacji, co oznacza, że będziesz musiał ponowić wiele prób. Zobacz dlaczego upsert jest tak skomplikowany , który omawia ten przypadek bardziej szczegółowo.

To podejście jest również poddawane utracie aktualizacji w izolacji read committed, chyba że aplikacja sprawdzi, czy dany wiersz ma wpływ na dany wiersz insert lub update.

Z PostgreSQL 9.1 można to osiągnąć za pomocą zapisu CTE (common table expression):

WITH new_values (id, field1, field2) as (
  values 
     (1, 'A', 'X'),
     (2, 'B', 'Y'),
     (3, 'C', 'Z')

),
upsert as
( 
    update mytable m 
        set field1 = nv.field1,
            field2 = nv.field2
    FROM new_values nv
    WHERE m.id = nv.id
    RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1 
                  FROM upsert up 
                  WHERE up.id = new_values.id)

Zobacz te wpisy na blogu:

• Upserting via Writeable CTE
• WAITING FOR 9.1-WRITABLE CTE
• DLACZEGO UPSERT JEST TAKI SKOMPLIKOWANY?

Zauważ, że to rozwiązanie nie zapobiega łamaniu unikalnych kluczy, ale nie jest podatne na utracone aktualizacje.
Zobacz kontynuację Craiga Ringera on dba.stackexchange.com

W PostgreSQL 9.5 i nowszych można użyć INSERT ... ON CONFLICT UPDATE.

Zobacz dokumentację.

MySQL INSERT ... ON DUPLICATE KEY UPDATE można bezpośrednio przeformułować na ON CONFLICT UPDATE. Podobnie jak standardowa składnia SQL, oba są rozszerzeniami specyficznymi dla bazy danych. istnieją dobre powody, dla których MERGE nie został użyty do tego , Nowa składnia nie została stworzona tylko dla Zabawy. (Składnia MySQL ma również problemy, które oznaczają, że nie został przyjęty bezpośrednio).

Np. podane ustawienia:

CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);

MySQL zapytanie:

INSERT INTO tablename (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

Staje się:

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

Różnice:

• Musisz podać nazwę kolumny (lub unikalną nazwę ograniczenia), której chcesz użyć do sprawdzenia unikalności. To jest ON CONFLICT (columnname) DO

• Należy użyć słowa kluczowego SET, tak jakby było to normalne UPDATE wyrażenie

Ma też kilka fajnych funkcji:

• Możesz mieć WHERE klauzulę na swoim UPDATE (pozwalającą skutecznie przekształcić ON CONFLICT UPDATE w ON CONFLICT IGNORE dla pewnego wartości)

• Wartości proponowane do wstawiania są dostępne jako zmienna wiersza EXCLUDED, która ma taką samą strukturę jak tabela docelowa. Oryginalne wartości w tabeli można uzyskać, używając nazwy tabeli. Tak więc w tym przypadku EXCLUDED.c będzie 10 (ponieważ to właśnie próbowaliśmy wstawić), a {[17] } będzie 3 ponieważ jest to bieżąca wartość w tabeli. W wyrażeniach SET i WHERE można użyć jednego lub obu tych wyrażeń.

Dla tła na upsert zobacz Jak UPSERT (MERGE, INSERT ... Przy DUPLICATE UPDATE) w PostgreSQL?

Szukałem tego samego, kiedy tu przyszedłem, ale brak ogólnej funkcji "upsert" trochę mnie niepokoi, więc pomyślałem, że możesz po prostu przekazać aktualizację i wstawić sql jako argumenty na tej funkcji z podręcznika

To by wyglądało tak:

CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
    RETURNS VOID
    LANGUAGE plpgsql
AS $$
BEGIN
    LOOP
        -- first try to update
        EXECUTE sql_update;
        -- check if the row is found
        IF FOUND THEN
            RETURN;
        END IF;
        -- not found so insert the row
        BEGIN
            EXECUTE sql_insert;
            RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing and loop
        END;
    END LOOP;
END;
$$;

I być może zrobić to, co początkowo chciał zrobić, wsad "upsert", można użyć Tcl podzielić sql_update i zapętlić poszczególne aktualizacje, hit preformance będzie bardzo mały zobacz http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

Najwyższym kosztem jest wykonanie zapytania z twojego kodu, po stronie bazy danych koszt wykonania jest znacznie mniejszy

Nie ma prostego polecenia, aby to zrobić.

Najbardziej poprawnym podejściem jest użycie funkcji, takiej jak ta z docs .

Innym rozwiązaniem (choć nie tak bezpiecznym) jest wykonanie aktualizacji z powrotem, sprawdzenie, które wiersze były aktualizacjami i wstawienie reszty

Coś w rodzaju:

update table
set column = x.column
from (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column)
where table.id = x.id
returning id;

Zakładając, że ID:2 zostało zwrócone:

insert into table (id, column) values (1, 'aa'), (3, 'cc');

Oczywiście, że prędzej czy później (w środowisku równoległym), ponieważ jest tu wyraźny stan rasy, ale zazwyczaj to zadziała.

Oto dłuższy i bardziej wyczerpujący artykuł na ten temat .

Osobiście ustawiłem "regułę" dołączoną do oświadczenia insert. Załóżmy, że masz tabelę "dns", która rejestruje trafienia dns na klienta na podstawie czasu:

CREATE TABLE dns (
    "time" timestamp without time zone NOT NULL,
    customer_id integer NOT NULL,
    hits integer
);

Chcesz móc ponownie wstawiać wiersze ze zaktualizowanymi wartościami lub tworzyć je, jeśli jeszcze nie istniały. Keyed na customer_id i czas. Coś takiego:

CREATE RULE replace_dns AS 
    ON INSERT TO dns 
    WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") 
            AND (dns.customer_id = new.customer_id)))) 
    DO INSTEAD UPDATE dns 
        SET hits = new.hits 
        WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));

Update: może się to nie udać, jeśli występują jednoczesne wstawki, ponieważ wygeneruje wyjątki unique_violation. Jednak niezakończona transakcja będzie kontynuowana i zakończy się sukcesem, a ty po prostu musisz powtórzyć zakończoną transakcję.

Jednakże, jeśli przez cały czas występuje mnóstwo wstawek, będziesz chciał umieścić blokadę tabeli wokół instrukcji insert: blokowanie wyłączne SHARE ROW zapobiegnie operacjom, które mogłyby wstawiać, usuwać lub aktualizować wiersze w tabeli docelowej. Jednak aktualizacje, które nie aktualizują unikalnego klucza, są bezpieczne, więc jeśli żadna operacja tego nie zrobi, użyj zamków doradczych zamiast tego.

Również polecenie Kopiuj nie używa reguł, więc jeśli wstawiasz za pomocą COPY, musisz użyć wyzwalaczy.

I custom" upsert " funkcja powyżej, jeśli chcesz wstawić i zastąpić:

`

 CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text)

 RETURNS void AS
 $BODY$
 BEGIN
    -- first try to insert and after to update. Note : insert has pk and update not...

    EXECUTE sql_insert;
    RETURN;
    EXCEPTION WHEN unique_violation THEN
    EXECUTE sql_update; 
    IF FOUND THEN 
        RETURN; 
    END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
 ALTER FUNCTION upsert(text, text)
 OWNER TO postgres;`

A po wykonaniu, zrób coś takiego:

SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$)

Jest ważne, aby umieścić podwójny Dolar-przecinek, aby uniknąć błędów kompilatora

Sprawdź prędkość...

Mam ten sam problem z zarządzaniem ustawieniami konta, co parami wartości nazwy. Kryteria projektowe są takie, że różni klienci mogą mieć różne zestawy ustawień.

Moim rozwiązaniem, podobnym do JWP, jest masowe kasowanie i zastępowanie, generowanie rekordu scalania w aplikacji.

Jest to dość kuloodporne, niezależne od platformy i ponieważ nigdy nie ma więcej niż około 20 ustawień na klienta, jest to tylko 3 dość niskie Ładowanie wywołań db - prawdopodobnie najszybsza Metoda.

The alternatywa aktualizacji pojedynczych wierszy-sprawdzanie wyjątków, a następnie wstawianie-lub jakaś kombinacja jest ohydny kod, powolny i często łamie, ponieważ (jak wspomniano powyżej) niestandardowe obsługa wyjątków SQL zmienia się z db na db - lub nawet release do release.

 #This is pseudo-code - within the application:
 BEGIN TRANSACTION - get transaction lock
 SELECT all current name value pairs where id = $id into a hash record
 create a merge record from the current and update record
  (set intersection where shared keys in new win, and empty values in new are deleted).
 DELETE all name value pairs where id = $id
 COPY/INSERT merged records 
 END TRANSACTION

Podobna do najbardziej lubianej odpowiedzi, ale działa nieco szybciej:

WITH upsert AS (UPDATE spider_count SET tally=1 WHERE date='today' RETURNING *)
INSERT INTO spider_count (spider, tally) SELECT 'Googlebot', 1 WHERE NOT EXISTS (SELECT * FROM upsert)

(Źródło: http://www.the-art-of-web.com/sql/upsert/)

CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying)
  RETURNS boolean AS
$BODY$
BEGIN
    UPDATE users SET name = _name WHERE id = _id;
    IF FOUND THEN
        RETURN true;
    END IF;
    BEGIN
        INSERT INTO users (id, name) VALUES (_id, _name);
    EXCEPTION WHEN OTHERS THEN
            UPDATE users SET name = _name WHERE id = _id;
        END;
    RETURN TRUE;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE STRICT

UPDATE zwróci liczbę zmodyfikowanych wierszy. Jeśli używasz JDBC (Java), możesz sprawdzić tę wartość pod kątem 0 i, jeśli żadne wiersze nie zostały naruszone, zamiast tego Uruchom INSERT. Jeśli używasz innego języka programowania, być może Liczba zmodyfikowanych wierszy nadal może być uzyskana, sprawdź dokumentację.

To może nie być tak eleganckie, ale masz znacznie prostszy SQL, który jest bardziej trywialny w użyciu z kodu wywołującego. Inaczej, jeśli piszesz skrypt 10 liniowy w PL / PSQL, to prawdopodobnie powinieneś mieć jednostkowy test tego lub innego rodzaju tylko dla niego.

Używam tej funkcji merge

CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
  RETURNS void AS
$BODY$
BEGIN
    IF EXISTS(SELECT a FROM tabla WHERE a = key)
        THEN
            UPDATE tabla SET b = data WHERE a = key;
        RETURN;
    ELSE
        INSERT INTO tabla(a,b) VALUES (key, data);
        RETURN;
    END IF;
END;
$BODY$
LANGUAGE plpgsql

Zgodnie z dokumentacją PostgreSQL instrukcji INSERT , Obsługa sprawy ON DUPLICATE KEY nie jest obsługiwana. Ta część składni jest zastrzeżonym rozszerzeniem MySQL.

Edit: to nie działa zgodnie z oczekiwaniami. W przeciwieństwie do przyjętej odpowiedzi, powoduje to unikalne naruszenia klucza, gdy dwa procesy wywołują jednocześnie upsert_foo.

CREATE TABLE foo (k INT PRIMARY KEY, v TEXT);

CREATE FUNCTION update_foo(k INT, v TEXT)
RETURNS SETOF INT AS $$
    UPDATE foo SET v = $2 WHERE k = $1 RETURNING $1
$$ LANGUAGE sql;

CREATE FUNCTION upsert_foo(k INT, v TEXT)
RETURNS VOID AS $$
    INSERT INTO foo
        SELECT $1, $2
        WHERE NOT EXISTS (SELECT update_foo($1, $2))
$$ LANGUAGE sql;

UPDATE należy wykonać w osobnej procedurze, ponieważ niestety jest to błąd składni:

... WHERE NOT EXISTS (UPDATE ...)

Teraz działa zgodnie z życzeniem:

SELECT upsert_foo(1, 'hi');
SELECT upsert_foo(1, 'bye');
SELECT upsert_foo(3, 'hi');
SELECT upsert_foo(3, 'bye');

Do scalania małych zbiorów, użycie powyższej funkcji jest w porządku. Jeśli jednak łączysz duże ilości danych, sugerowałbym zajrzenie do http://mbk.projects.postgresql.org

Obecna najlepsza praktyka, o której Wiem, to:

1. skopiuj nowe / zaktualizowane dane do tabeli temp (jasne, lub możesz wstawić, jeśli koszt jest ok)
2. Acquire Lock [opcjonalne] (zaleca się doradzanie w przypadku zamków stołowych, IMO)
3. Połącz. (the fun part)