Ein Upsert mit INSERT ... ON CONFLICT schreiben

Ziel

Eine Zeile einfügen, wenn ihr Schlüssel neu ist, und andernfalls die bestehende Zeile aktualisieren, ohne Race Condition zwischen SELECT und INSERT und ohne Retry-Schleifen im Anwendungscode.

Voraussetzungen

Ein Unique-Index oder eine Unique-Constraint auf den Schlüsselspalten (die INSERT-Dokumentation nennt den für die Konflikterkennung gewählten Index den Arbiter-Index und beschreibt, wie er aus den aufgeführten Spalten, einem Index-Prädikat oder ON CONSTRAINT name abgeleitet wird), sowie eine Entscheidung, welche Spalten ein Update überschreiben darf.

Schritte

1. Das einfache INSERT mit allen Spalten schreiben.
2. ON CONFLICT (key_columns) anhängen und dabei die Spalten des Unique-Index benennen. Bei einem partiellen Unique-Index dessen WHERE-Prädikat nach der Spaltenliste wiederholen, damit die Ableitung es findet.
3. Die Aktion wählen: DO NOTHING, wenn eine bestehende Zeile unangetastet bleiben muss; DO UPDATE SET col = EXCLUDED.col, updated_at = now(), um ausgewählte Spalten zu überschreiben. EXCLUDED ist die Zeile, die zum Einfügen vorgeschlagen wurde.
4. Wirkungslose Updates mit WHERE t.col IS DISTINCT FROM EXCLUDED.col nach der SET-Liste absichern, damit unveränderte Zeilen nicht neu geschrieben werden; jedes Update erzeugt eine tote Zeilenversion und löst Trigger aus.
5. RETURNING id ergänzen, wenn der Aufrufer den Schlüssel der eingefügten oder aktualisierten Zeile braucht.
6. Für Batches ein INSERT mit vielen VALUES-Zeilen oder INSERT ... SELECT verwenden; nicht über einzelne Upserts pro Zeile schleifen.
7. Wenn die Logik mehrere Zweige hat (Update bei Treffer und Bedingung, sonst Löschen bei Treffer, Einfügen bei keinem Treffer), MERGE INTO target USING source ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ... schreiben; die MERGE-Dokumentation listet die Aktionen WHEN MATCHED, WHEN NOT MATCHED und WHEN NOT MATCHED BY SOURCE auf.

Erwartetes Ergebnis

Eine Anweisung pro Upsert, keine doppelten Zeilen bei gleichzeitigen Schreibern, weil der Konflikt innerhalb der Anweisung über den Unique-Index erkannt wird, und die Menge toter Zeilenversionen bleibt auf tatsächlich geänderte Zeilen begrenzt.

Grenzen und Prüfbasis

ON CONFLICT DO UPDATE benötigt ein Konfliktziel; behandelt werden nur Konflikte auf den davon gewählten Arbiter-Indexen (eine Verletzung eines anderen Unique-Index löst weiterhin einen Fehler aus), und nur NOT DEFERRABLE Constraints und Unique-Indexe können als Arbiter dienen. DO NOTHING ohne Konfliktziel behandelt Konflikte mit allen brauchbaren Constraints. Die Dokumentation zu Sequenzen hält fest, dass ein INSERT mit ON-CONFLICT-Klausel die Zeile einschliesslich der nextval-Aufrufe berechnet, bevor der Konflikt erkannt wird, sodass Identity-Werte auch von Zeilen verbraucht werden, die am Ende nicht eingefügt werden. Die MERGE-Dokumentation besagt, dass unter Nebenläufigkeit die üblichen Isolationsregeln gelten, und verweist auf INSERT ... ON CONFLICT als die Anweisung, die bei einem gleichzeitigen INSERT ein UPDATE ausführen kann; die beiden sind nicht austauschbar. Es werden keine Zeitangaben behauptet.

Wann MERGE das richtige Werkzeug ist

MERGE und INSERT ... ON CONFLICT sind unter Nebenläufigkeit nicht austauschbar. MERGE prüft seinen Treffer gegen einen Snapshot und führt nicht das spekulative Einfügen aus, auf das sich ON CONFLICT stützt; daher können zwei Sitzungen, die denselben neuen Schlüssel mergen, beide den Zweig WHEN NOT MATCHED nehmen, wobei die zweite dann an einer Unique-Verletzung scheitert. MERGE verwenden, wenn jeweils ein Schreiber den Schlüsselbereich besitzt: Batch-Ladevorgänge, Migrationen, Single-Threaded-Importer oder im Voraus gesperrte Zeilen. ON CONFLICT verwenden, wann immer gleichzeitige Schreiber möglich sind; mehrere Update-Zweige passen als CASE-Ausdrücke in die SET-Liste, und ein Lösch-Zweig wird zu einer eigenen Anweisung. WHEN NOT MATCHED BY SOURCE und RETURNING bei MERGE setzen PostgreSQL 17 oder neuer voraus.