Wie kann UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL durchgeführt werden?

Question

Eine sehr häufig gestellte Frage hier ist, wie man ein Upsert durchführt, was MySQL als INSERT ... ON DUPLICATE UPDATE bezeichnet und was der Standard als Teil der MERGE-Operation unterstützt.

Da PostgreSQL es nicht direkt unterstützt (vor pg 9.5), wie macht man das? Betrachten Sie das Folgende:

CREATE TABLE testtable (
    id integer PRIMARY KEY,
    somedata text NOT NULL
);

INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');

Stellen Sie sich nun vor, Sie möchten die Tupel (2, 'Joe'), (3, 'Alan') "upserten", sodass der neue Tabelleninhalt wäre:

(1, 'fred'),
(2, 'Joe'),    -- Wert des vorhandenen Tupels geändert
(3, 'Alan')    -- Neues Tupel hinzugefügt

Das ist es, worüber die Leute sprechen, wenn sie über ein upsert diskutieren. Wesentlich ist, dass jeder Ansatz sicher ist in Anbetracht mehrerer Transaktionen, die an der gleichen Tabelle arbeiten - entweder durch die Verwendung von expliziten Sperren oder anderweitiges Verteidigen gegen die resultierenden Rennbedingungen.

Dieses Thema wird ausführlich diskutiert unter Insert, on duplicate update in PostgreSQL?, aber das ist über Alternativen zur MySQL-Syntax und hat im Laufe der Zeit eine ziemliche Menge an nicht verwandten Details angesammelt. Ich arbeite an abschließenden Antworten.

Diese Techniken sind auch nützlich für "einfügen, wenn nicht vorhanden, ansonsten nichts tun", d.h. "insert ... on duplicate key ignore".

Answer 1

Hier sind einige Beispiele für insert ... on conflict ... (pg 9.5+) :

• Insert, on conflict - do nothing.

  insert into dummy(id, name, size) values(1, 'new_name', 3)
  on conflict do nothing;`  

• Insert, on conflict - do update, specify conflict target via column.

  insert into dummy(id, name, size) values(1, 'new_name', 3)
  on conflict(id)
  do update set name = 'new_name', size = 3;  

• Insert, on conflict - do update, specify conflict target via constraint name.

  insert into dummy(id, name, size) values(1, 'new_name', 3)
  on conflict on constraint dummy_pkey
  do update set name = 'new_name', size = 4;

Answer 2

Ich versuche, mit einer anderen Lösung für das Single-Insertion-Problem mit den Pre-9.5-Versionen von PostgreSQL beizutragen. Die Idee ist einfach, zuerst das Einfügen zu versuchen und im Falle, dass der Datensatz bereits vorhanden ist, ihn zu aktualisieren:

do $$
begin 
  insert into testtable(id, somedata) values(2,'Joe');
exception when unique_violation then
  update testtable set somedata = 'Joe' where id = 2;
end $$;

Beachten Sie, dass diese Lösung nur angewendet werden kann, wenn keine Zeilen aus der Tabelle gelöscht werden.

Über die Effizienz dieser Lösung weiß ich nichts, aber sie scheint mir vernünftig genug zu sein.

Answer 3

MERGE in PostgreSQL v. >=15

Seit PostgreSQL v. 15 ist es möglich, den MERGE Befehl zu verwenden. Tatsächlich wurde dies als die erste der Hauptverbesserungen dieser neuen Version vorgestellt.

Es verwendet eine WHEN MATCHED / WHEN NOT MATCHED Bedingung, um das Verhalten zu wählen, wenn eine vorhandene Zeile mit den gleichen Kriterien vorhanden ist.

Es ist sogar besser als das Standard UPSERT, da das neue Feature volle Kontrolle über das INSERT, UPDATE oder DELETE von Zeilen in Stapeln ermöglicht.

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value)

Answer 4

SQLAlchemy upsert für Postgres >=9.5

Da der obige große Beitrag viele verschiedene SQL-Ansätze für Postgres-Versionen abdeckt (nicht nur nicht-9.5 wie in der Frage), möchte ich hinzufügen, wie man es in SQLAlchemy macht, wenn man Postgres 9.5 verwendet. Anstatt Ihren eigenen upsert zu implementieren, können Sie auch die Funktionen von SQLAlchemy verwenden (die in SQLAlchemy 1.1 hinzugefügt wurden). Persönlich würde ich empfehlen, diese zu verwenden, wenn möglich. Nicht nur wegen der Bequemlichkeit, sondern auch weil es PostgreSQL ermöglicht, etwaige Wettlaufbedingungen zu behandeln, die auftreten könnten.

Querverweis von einer anderen Antwort, die ich gestern gegeben habe (https://stackoverflow.com/a/44395983/2156909)

SQLAlchemy unterstützt jetzt ON CONFLICT mit zwei Methoden on_conflict_do_update() und on_conflict_do_nothing():

Auszug aus der Dokumentation:

from sqlalchemy.dialects.postgresql import insert

stmt = insert(my_table).values(user_email='a@b.com', data='eingefügte Daten')
stmt = stmt.on_conflict_do_update(
    index_elements=[my_table.c.user_email],
    index_where=my_table.c.user_email.like('%@gmail.com'),
    set_=dict(data=stmt.excluded.data)
    )
conn.execute(stmt)

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html?highlight=conflict#insert-on-conflict-upsert

Answer 5

MIT UPD ALS (UPDATE TEST_TABLE SET SOME_DATA = 'Joe' WHERE ID = 2 
ZURÜCKGEBEND ID),
EINFÜGEN ALS (AUSWÄHLEN '2', 'Joe' WO NICHT VORHANDEN (AUSWAHL * FROM UPD))
EINFÜGEN IN TEST_TABLE(ID, SOME_DATA) AUSWÄHLEN * FROM INS

Auf Postgresql 9.3 getestet