PostgreSQL hat kein Schlüsselwort UPSERT. Du schreibst INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. Würde die neue Zeile einen Unique Constraint auf dieser Spalte verletzen, aktualisiert PostgreSQL stattdessen die bestehende Zeile. DO NOTHING überspringt sie.
A-100 gab es schon, sein Preis wurde also auf 12.5 geändert. B-200 war neu und wurde eingefügt. Eine Anweisung erledigt beides, und sie bleibt korrekt, wenn andere Sitzungen gleichzeitig dieselben Schlüssel einfügen.
Eine Zeile einfügen oder aktualisieren
Die Anweisung hat drei Teile:
INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
- Das Conflict Target nennt die Spalten eines eindeutigen Index oder Constraints, etwa des Primärschlüssels. Es muss genau zu einem passen, sonst meldet PostgreSQL "there is no unique or exclusion constraint matching the ON CONFLICT specification". Du kannst stattdessen den Constraint nennen:
ON CONFLICT ON CONSTRAINT products_pkey. EXCLUDEDist die Zeile, die du einfügen wolltest.EXCLUDED.priceist der neue Preis undproducts.priceder schon gespeicherte.DO UPDATE SETändert nur die Spalten, die du aufführst. Die übrigen Spalten der bestehenden Zeile behalten ihre Werte.
Ein eindeutiger Index mit WHERE Klausel (ein partieller Index) funktioniert auch als Target, wenn du seine Bedingung wiederholst: ON CONFLICT (email) WHERE deleted_at IS NULL.
Duplikate mit DO NOTHING überspringen
DO NOTHING fügt die neuen Zeilen ein und verwirft die kollidierenden, ohne Fehler. Ohne Conflict Target überspringt es eine Zeile, die irgendeinen Unique Constraint verletzt.
RETURNING listet nur lin@example.com auf. Eine übersprungene Zeile wird nicht zurückgegeben, DO NOTHING ... RETURNING zeigt also, welche Zeilen neu waren.
Einen Zähler erhöhen
Um einen gespeicherten Wert zu erhöhen statt ihn zu ersetzen, addierst du EXCLUDED zum aktuellen Wert. Qualifiziere den aktuellen Wert mit dem Tabellennamen. Innerhalb von DO UPDATE passt ein bloßes views sowohl auf die Tabelle als auch auf EXCLUDED, und PostgreSQL bricht mit "column reference "views" is ambiguous" ab.
Die erste Anweisung fügt die Zeile mit 1 ein, die nächsten beiden addieren dazu, /home endet also bei 3.
Nur aktualisieren, wenn sich der Wert geändert hat
Ein WHERE nach DO UPDATE SET entscheidet pro Zeile, ob das Update läuft. Ist es falsch, bleibt die Zeile, wie sie ist, und zählt weder als eingefügt noch als aktualisiert. So behältst du nur die neuesten Daten oder vermeidest eine neue Zeilenversion, wenn sich nichts geändert hat.
A-100 behält 10, weil die eingehende Zeile älter ist. B-200 übernimmt 25. Um Updates zu überspringen, die dieselben Werte schreiben würden, nimmst du WHERE prices.price IS DISTINCT FROM EXCLUDED.price, das auch mit NULL umgehen kann.
Eingefügte von aktualisierten Zeilen unterscheiden
RETURNING funktioniert bei einem Upsert wie bei jedem Insert. Um die beiden Fälle zu unterscheiden, liest du die Systemspalte xmax, die für eine von dieser Anweisung eingefügte Zeile 0 ist:
A-100 kommt mit 8 und inserted falsch zurück, C-300 mit 7 und wahr. xmax ist ein internes Detail und keine dokumentierte API, betrachte es also als Hilfsmittel. Die Seite zu RETURNING behandelt die Klausel ausführlich.
Fehler, denen du begegnen wirst
- "ON CONFLICT DO UPDATE command cannot affect row a second time": Derselbe Schlüssel kommt im
VALUESoderSELECTeiner Anweisung zweimal vor. Entferne Duplikate vorher, etwa mitSELECT DISTINCT ON (sku) .... - "column reference is ambiguous": Qualifiziere die Spalte in
SETAusdrücken und imWHEREmit dem Tabellennamen, wie oben. - Lücken in Identity- oder Serial-IDs: Ein Insert, der zum Update wird, hat schon einen Wert aus der Sequenz genommen, IDs überspringen also Zahlen. Das ist erwartet.
MERGE (ab PostgreSQL 15) kann auch einfügen oder aktualisieren, schützt aber nicht davor, dass zwei Sitzungen gleichzeitig denselben Schlüssel einfügen. Für Upserts per Schlüssel nimmst du ON CONFLICT. Die offizielle Referenz zu INSERT listet jede Option auf.
Häufig gestellte Fragen
Hat PostgreSQL eine UPSERT Anweisung?
Ein Schlüsselwort UPSERT gibt es nicht. PostgreSQL 9.5 hat INSERT ... ON CONFLICT eingeführt, das eine Zeile einfügt oder, wenn sie einen Unique Constraint verletzen würde, die bestehende Zeile aktualisiert (DO UPDATE) oder sie überspringt (DO NOTHING).
Ist INSERT ... ON CONFLICT bei gleichzeitigen Inserts sicher?
Ja. Jede vorgeschlagene Zeile endet entweder als Insert oder als Update, selbst wenn andere Sitzungen im selben Moment denselben Schlüssel einfügen. Ein SELECT gefolgt von einem INSERT oder UPDATE im Anwendungscode gibt diese Garantie nicht.
Soll ich für einen Upsert MERGE oder ON CONFLICT nehmen?
Nimm ON CONFLICT für Einfügen oder Aktualisieren per Schlüssel. MERGE (ab PostgreSQL 15) deckt mehr Fälle ab, etwa das Löschen passender Zeilen. Bei gleichzeitigen Inserts desselben Schlüssels kann es mit einer Unique Violation scheitern, was ON CONFLICT vermeidet.
Warum bekomme ich "there is no unique or exclusion constraint matching the ON CONFLICT specification"?
Die Spalten in ON CONFLICT (...) müssen genau zu einem eindeutigen Index oder Constraint passen. Leg einen UNIQUE Constraint oder einen eindeutigen Index auf diese Spalten an oder nenn die Spalten eines bestehenden.