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

Laut der PostgreSQL-Dokumentation des INSERT-Statements wird der Fall ON DUPLICATE KEY nicht unterstützt. Dieser Teil der Syntax ist eine proprietäre MySQL-Erweiterung.

Answer 2

Ich habe das gleiche Problem bei der Verwaltung von Kontoeinstellungen als Name-Wert-Paare. Die Designkriterien besagen, dass verschiedene Clients unterschiedliche Einstellungssets haben könnten.

Meine Lösung, ähnlich wie bei JWP, besteht darin, Massenlöschungen und -ersetzungen durchzuführen und den Merge-Record innerhalb Ihrer Anwendung zu generieren.

Dies ist ziemlich sicher, plattformunabhängig und da nie mehr als etwa 20 Einstellungen pro Client vorhanden sind, handelt es sich nur um 3 recht geringe Datenbankaufrufe - wahrscheinlich die schnellste Methode.

Die Alternative zum Aktualisieren einzelner Zeilen - Überprüfen von Ausnahmen und dann Einfügen - oder einer Kombination aus beiden ist hässlicher Code, langsam und bricht oft ab, weil (wie oben erwähnt) die nicht standardmäßige SQL-Ausnahmebehandlung sich je nach Datenbank oder sogar Version ändert.

 #Dies ist Pseudocode - innerhalb der Anwendung:
BEGIN TRANSACTION - Transaktionssperre erhalten
Wählen Sie alle aktuellen Name-Wert-Paare aus, bei denen die ID = $id in einen Haschrercord ist
Erstellen Sie einen Merge-Record aus dem aktuellen und dem Update-Record
(Überschneidung setzen, wobei gemeinsame Schlüssel im neuen Fenster und leere Werte im neuen gelöscht werden).
LÖSCHEN Sie alle Name-Wert-Paare, bei denen die ID = $id ist
KOPIEREN/EINFÜGEN der zusammengeführten Datensätze
END TRANSACTION

Answer 3

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

Answer 4

Für das Zusammenführen kleiner Sets ist die Verwendung der obigen Funktion in Ordnung. Wenn Sie jedoch große Datenmengen zusammenführen, empfehle ich Ihnen, sich http://mbk.projects.postgresql.org anzusehen

Derzeit beste mir bekannte Praxis ist:

1. Kopieren Sie neue/aktualisierte Daten in eine temporäre Tabelle (oder Sie können INSERT verwenden, wenn die Kosten in Ordnung sind)
2. Sperre erhalten [optional] (aus meiner Sicht ist eine beratende Sperre einer Tabellensperre vorzuziehen)
3. Zusammenführen. (der lustige Teil)

Answer 5

UPDATE gibt die Anzahl der geänderten Zeilen zurück. Wenn Sie JDBC (Java) verwenden, können Sie diesen Wert dann mit 0 vergleichen und bei keiner betroffenen Zeile stattdessen INSERT ausführen. Wenn Sie eine andere Programmiersprache verwenden, kann die Anzahl der geänderten Zeilen möglicherweise immer noch abgerufen werden, überprüfen Sie die Dokumentation.

Dies ist möglicherweise nicht so elegant, aber Sie haben eine wesentlich einfachere SQL-Anweisung, die von dem aufrufenden Code einfacher zu verwenden ist. Falls Sie das zehnzeilige Skript in PL/PSQL schreiben, sollten Sie wahrscheinlich einen Unittest von einer oder anderer Art nur für dieses Skript allein haben.