Einfügen, bei Duplikaten aktualisieren in PostgreSQL?

Question

Vor mehreren Monaten habe ich aus einer Antwort auf Stack Overflow gelernt, wie man mehrere Updates gleichzeitig in MySQL mit der folgenden Syntax durchführen kann:

INSERT INTO Tabelle (id, Feld, Feld2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);

Jetzt bin ich zu PostgreSQL gewechselt und anscheinend ist das nicht korrekt. Es verweist auf alle richtigen Tabellen, daher nehme ich an, dass es nur darum geht, dass unterschiedliche Schlüsselwörter verwendet werden, aber ich bin mir nicht sicher, wo dies in der PostgreSQL-Dokumentation behandelt wird.

Zur Klarstellung: Ich möchte mehrere Dinge einfügen und wenn sie bereits vorhanden sind, sie aktualisieren.

Answer 1

PostgreSQL seit der Version 9.5 hat UPSERT Syntax, mit ON CONFLICT Klausel. mit der folgenden Syntax (ähnlich wie MySQL)

INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;

---

Die Suche in den E-Mail-Gruppenarchiven von PostgreSQL nach "upsert" führt zur Beispiel einer möglichen Vorgehensweise im Handbuch:

Beispiel 38-2. Ausnahmen mit UPDATE/INSERT

Dieses Beispiel verwendet Ausnahmebehandlung zum Durchführen von UPDATE oder INSERT, wie erforderlich:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- Versuchen Sie zunächst den Schlüssel zu aktualisieren
        -- Beachten Sie, dass "a" eindeutig sein muss
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- Nicht vorhanden, also versuchen Sie den Schlüssel einzufügen
        -- Wenn jemand anderes den gleichen Schlüssel gleichzeitig einfügt,
        -- könnten wir einen Eindeutigkeitsfehler erhalten
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- nichts tun und erneut versuchen das UPDATE durchzuführen
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

---

Möglicherweise gibt es ein Beispiel, wie dies in Bulk mit CTEs in 9.1 und höher gemacht werden kann, in der hackers Mailingliste:

WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;

Sehen Sie sich a_horse_with_no_name's Antwort für ein klareres Beispiel an.

Answer 2

Warnung: Dies ist nicht sicher, wenn es gleichzeitig aus mehreren Sitzungen ausgeführt wird (siehe unten stehende Warnungen).

---

Ein weiterer intelligenter Weg, um ein "UPSERT" in PostgreSQL durchzuführen, besteht darin, zwei aufeinanderfolgende UPDATE/INSERT-Anweisungen auszuführen, die jeweils darauf ausgelegt sind, erfolgreich zu sein oder keine Auswirkungen zu haben.

UPDATE tabelle SET feld='C', feld2='Z' WHERE id=3;
INSERT INTO tabelle (id, feld, feld2)
       SELECT 3, 'C', 'Z'
       WHERE NOT EXISTS (SELECT 1 FROM tabelle WHERE id=3);

Das UPDATE wird erfolgreich sein, wenn bereits eine Zeile mit "id=3" existiert, andernfalls hat es keine Auswirkung.

Das INSERT wird nur erfolgreich sein, wenn die Zeile mit "id=3" noch nicht existiert.

Sie können diese beiden in einen einzelnen String kombinieren und sie beide mit einer einzelnen SQL-Anweisung von Ihrer Anwendung ausführen. Es wird dringend empfohlen, sie zusammen in einer einzelnen Transaktion auszuführen.

Dies funktioniert sehr gut, wenn es isoliert oder auf einer gesperrten Tabelle ausgeführt wird, ist jedoch anfällig für Wettlaufbedingungen, die bedeuten, dass es trotzdem mit einem Fehler für doppelten Schlüssel fehlschlagen könnte, wenn eine Zeile gleichzeitig eingefügt wird, oder mit keiner eingefügte Zeile enden könnte, wenn eine Zeile gleichzeitig gelöscht wird. Eine SERIALIZABLE Transaktion in PostgreSQL 9.1 oder höher wird dies zuverlässig behandeln, allerdings mit einer sehr hohen Serienfehlerrate, was bedeutet, dass Sie es möglicherweise viele Male wiederholen müssen. Siehe warum ist upsert so kompliziert, das diesen Fall genauer diskutiert.

Dieser Ansatz ist auch anfällig für verlorene Updates in der read committed Isolation, es sei denn, die Anwendung überprüft die betroffenen Zeilenzahlen und stellt fest, dass entweder das insert oder das update eine Zeile betroffen hat.

Answer 3

Mit PostgreSQL 9.1 kann dies mit einem beschreibbaren CTE (common table expression) erreicht werden:

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)

Siehe diese Blog-Einträge:

• Upserting via Writeable CTE
• WAITING FOR 9.1 – WRITABLE CTE
• WHY IS UPSERT SO COMPLICATED?

---

Beachten Sie, dass diese Lösung nicht ein Verstoß gegen einen eindeutigen Schlüssel verhindert, aber sie ist nicht anfällig für verlorene Updates.
Siehe das Follow-up von Craig Ringer auf dba.stackexchange.com

Answer 4

In PostgreSQL 9.5 und neuer können Sie INSERT ... ON CONFLICT UPDATE verwenden.

Siehe die Dokumentation.

Ein MySQL INSERT ... ON DUPLICATE KEY UPDATE kann direkt in ein ON CONFLICT UPDATE umgeschrieben werden. Beides sind keine SQL-Standard-Syntaxen, sondern datenbankspezifische Erweiterungen. Es gibt gute Gründe warum MERGE dafür nicht verwendet wurde, eine neue Syntax wurde nicht einfach nur aus Spaß erstellt. (MySQLs Syntax hat auch Probleme, weshalb sie nicht direkt übernommen wurde).

Zum Beispiel mit folgender Einrichtung:

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

wird die MySQL-Abfrage:

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

zu:

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

Unterschiede:

• Sie müssen den Spaltennamen (oder den eindeutigen Schlüsselnamen) angeben, der für die Eindeutigkeitsprüfung verwendet werden soll. Das ist das ON CONFLICT (Spaltenname) DO

• Das Schlüsselwort SET muss verwendet werden, als ob es sich um eine normale UPDATE-Anweisung handeln würde

Es gibt auch einige nette Funktionen:

• Sie können eine WHERE-Klausel in Ihrem UPDATE haben (damit können Sie ON CONFLICT UPDATE effektiv in ON CONFLICT IGNORE für bestimmte Werte umwandeln)

• Die vorgeschlagenen Einfügungswerte sind als die Zeilenvariable EXCLUDED verfügbar, die die gleiche Struktur wie die Zieltabelle hat. Sie können die Originalwerte in der Tabelle erhalten, indem Sie den Tabellennamen verwenden. Also in diesem Fall wird EXCLUDED.c 10 sein (weil das ist, was wir einfügen wollten) und "Tabelle".c wird 3 sein, weil das der aktuelle Wert in der Tabelle ist. Sie können beide in den SET-Ausdrücken und der WHERE-Klausel verwenden.

Für weitere Informationen zu Upsert siehe Wie kann man in PostgreSQL UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) durchführen?

Answer 5

Als ich hierher kam, suchte ich nach dem Gleichen, aber das Fehlen einer generischen "upsert"-Funktion hat mich ein wenig gestört, also dachte ich, dass du einfach das Update und das Insert-SQL als Argumente an diese Funktion im Handbuch übergeben könntest.

Das würde so aussehen:

CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
    RETURNS VOID
    LANGUAGE plpgsql
AS $$
BEGIN
    LOOP
        -- zuerst versuche zu aktualisieren
        EXECUTE sql_update;
        -- prüfe, ob die Zeile gefunden wurde
        IF FOUND THEN
            RETURN;
        END IF;
        -- wenn nicht gefunden, füge die Zeile ein
        BEGIN
            EXECUTE sql_insert;
            RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- mache nichts und gehe in die Schleife
        END;
    END LOOP;
END;
$$;

Und vielleicht, um das zu tun, was du ursprünglich tun wolltest, das "upsert" zu stapeln, könntest du Tcl verwenden, um das sql_update zu splitten und die einzelnen Updates durchzuführen, der Leistungsverlust wird sehr gering sein, siehe http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

Der größte Kostenpunkt ist das Ausführen der Abfrage von deinem Code aus, auf der Datenbankseite ist die Ausführungskosten viel geringer