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

Es gibt keinen einfachen Befehl, um dies zu tun.

Der korrekteste Ansatz ist die Verwendung einer Funktion, wie z.B. die aus Docs.

Eine andere Lösung (obwohl nicht so sicher) ist ein Update mit Rückgabe zu machen, um zu überprüfen, welche Zeilen aktualisiert wurden, und den Rest einzufügen.

So etwas in der Art:

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;

Angenommen, die id:2 wurde zurückgegeben:

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

Natürlich wird es früher oder später scheitern (in einer gleichzeitigen Umgebung), da hier ein klarer Wettlauf besteht, aber normalerweise funktioniert es.

Hier ist ein längerer und umfassenderer Artikel zum Thema.

Answer 2

Ich verwende diese Funktion merge

ERSTELLEN ODER ERSETZEN FUNKTION merge_tabla(key INT, data TEXT)
  GIBT void ALS
$BODY$
BEGIN
    WENN VORHANDEN (SELECT a FROM tabla WHERE a = key)
        DANN
            UPDATE tabla SET b = data WHERE a = key;
        RETURN;
    SONST
        INSERT INTO tabla(a,b) VALUES (key, data);
        RETURN;
    END IF;
END;
$BODY$
SPRACHE plpgsql

Answer 3

Persönlich habe ich eine "Regel" mit dem insert-Befehl verknüpft. Angenommen, Sie hatten eine "dns"-Tabelle, die dns-Treffer pro Kunde basierend auf einer bestimmten Zeitbasis aufzeichnet:

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

Sie möchten in der Lage sein, Zeilen mit aktualisierten Werten neu einzufügen oder sie zu erstellen, wenn sie noch nicht vorhanden sind. Dabei wird der customer_id und die Zeit als Schlüssel verwendet. Etwas in dieser Art:

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: Dies hat das Potenzial, zu scheitern, wenn gleichzeitige Einfügungen stattfinden, da es zu unique_violation Ausnahmen kommen kann. Die nicht abgeschlossene Transaktion wird jedoch fortgesetzt und erfolgreich abgeschlossen, und Sie müssen die abgebrochene Transaktion einfach wiederholen.

Jedoch, wenn ständig viele Einfügungen stattfinden, möchten Sie vielleicht eine Tabellensperre um die insert-Befehle herum setzen: SHARE ROW EXCLUSIVE-Sperren verhindern alle Operationen, die Zeilen in Ihrer Ziel-Tabelle einfügen, löschen oder aktualisieren könnten. Updates, die den eindeutigen Schlüssel nicht aktualisieren, sind jedoch sicher, also wenn keine Operation dies tun wird, verwenden Sie stattdessen Bezeichnungssperren.

Außerdem verwendet der COPY-Befehl keine REGELN, daher müssen Sie bei Einfügungen mit COPY statt dessen Trigger verwenden.

Answer 4

Ähnlich wie die beliebteste Antwort, aber etwas schneller funktioniert:

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)

(Quelle: http://www.the-art-of-web.com/sql/upsert/)

Answer 5

Ich habe die "upsert" Funktion oben angepasst, wenn du INSERT AND REPLACE möchtest:

`

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

 RETURNS void AS
 $BODY$
 BEGIN
    -- Versuche zuerst einzufügen und dann zu aktualisieren. Hinweis: Einfügen hat ein Primärschlüssel und Aktualisieren nicht...

    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;

Und nach der Ausführung, mache etwas Ähnliches wie dies:

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

Es ist wichtig, doppelte Dollar-Kommas zu verwenden, um Compilerfehler zu vermeiden

• Überprüfe die Geschwindigkeit...