PostgreSQL einfügen oder aktualisieren, falls vorhanden

Wenn der Schlüssel neu ist, fügt PostgreSQL eine Zeile ein; existiert der Schlüssel bereits, wird die Zeile aktualisiert. Verwenden Sie dazu INSERT ... ON CONFLICT (UPSERT). Das Konfliktziel muss einer eindeutigen Einschränkung oder einem eindeutigen Index entsprechen, etwa einem Primärschlüssel. Bei einer Tabelle users, in der id der Primärschlüssel ist, fügt INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 24) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; den Benutzer 12 ein oder ersetzt in der vorhandenen Zeile diese drei Felder.

Die folgenden Beispiele setzen dieses Schema voraus: CREATE TABLE users (id integer PRIMARY KEY, name text NOT NULL, gender text NOT NULL, age integer NOT NULL);. Die SQL-Beispiele stehen direkt im Text, da sie einen PostgreSQL-Server benötigen. Das Python-Beispiel bildet lediglich die Entscheidung zwischen Einfügen und Aktualisieren ab.

Führen Sie dieses Beispiel aus:

users = {}

def upsert(user):
    key = user["id"]
    if key in users:
        users[key].update(user)
    else:
        users[key] = user.copy()

upsert({"id": 12, "name": "John Kane", "age": 23})
upsert({"id": 12, "name": "John Kane", "age": 24})
print(users[12])
print("rows:", len(users))

{'id': 12, 'name': 'John Kane', 'age': 24}
rows: 1

Dieses Python-Beispiel veranschaulicht nur die Entscheidung; es verbindet sich nicht mit PostgreSQL und führt dort kein SQL aus. Die SQL-Muster weiter unten sind Datenbankbeispiele und sollten mit einer Tabelle ausgeführt werden, die das angegebene Schema verwendet. Das unveränderte Ausgabe-Beispiel zeigt die Beschriftung rows: auf Englisch.

Mit UPDATE und anschließendem INSERT eine Zeile einfügen oder aktualisieren, falls sie vorhanden ist

Ein UPDATE mit anschließendem bedingtem INSERT ist ein Muster aus zwei Anweisungen. Führen Sie zuerst UPDATE users SET name = 'John Kane', gender = 'Male', age = 21 WHERE id = 12 RETURNING id; aus. Gibt die Anweisung eine Zeile zurück, wurde der vorhandene Benutzer aktualisiert. Gibt sie keine Zeile zurück, führen Sie INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 21); aus.

Die Anwendung oder Transaktion, die diese Anweisungen ausführt, muss anhand des Ergebnisses von UPDATE ... RETURNING entscheiden, ob das INSERT ausgeführt wird. Ein getrenntes UPDATE und INSERT bilden kein atomares UPSERT: Eine andere Transaktion kann zwischen den beiden Anweisungen denselben Schlüssel einfügen und dadurch einen Konflikt mit dem eindeutigen Schlüssel auslösen. Für den üblichen Einfüge-oder-Aktualisiere-Vorgang, der konkurrierende Schreibzugriffe berücksichtigen muss, sollten Sie das folgende INSERT ... ON CONFLICT bevorzugen.

Mit INSERT ... ON CONFLICT (UPSERT) eine Zeile einfügen oder aktualisieren, falls sie vorhanden ist

INSERT ... ON CONFLICT versucht, die Zeile einzufügen, und behandelt einen Konflikt mit einer Eindeutigkeitsbedingung innerhalb derselben Anweisung. Das Konfliktziel muss eine passende eindeutige Einschränkung oder einen eindeutigen Index bezeichnen. In diesem Beispiel ist id der Primärschlüssel, und EXCLUDED bezeichnet die zum Einfügen vorgeschlagene Zeile.

Verwenden Sie INSERT INTO users (id, name, gender, age) VALUES (12, 'John Kane', 'Male', 24) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age;, um die Zeile einzufügen oder ihren Namen, ihr Geschlecht und ihr Alter zu ersetzen, wenn id = 12 bereits vorhanden ist. Sollen nur ausgewählte Felder geändert werden, führen Sie nur diese Felder nach DO UPDATE SET auf. DO UPDATE SET age = EXCLUDED.age lässt beispielsweise den vorhandenen Namen und das Geschlecht unverändert.

Die Aktion bestimmt das Ergebnis: Bei einem neuen Schlüssel wird die angegebene Zeile eingefügt; bei einem vorhandenen Schlüssel werden die unter DO UPDATE SET genannten Felder aktualisiert. Wenn die vorhandene Zeile bei einem Konflikt unverändert bleiben soll, kann die Anweisung auch DO NOTHING verwenden. Hintergrundinformationen aus einer von der Community gepflegten Quelle finden Sie auf der UPSERT-Seite des PostgreSQL Wiki.

Mit einer Common Table Expression (CTE) eine Zeile einfügen oder aktualisieren, falls sie vorhanden ist

Eine datenändernde CTE kann die von einem UPDATE zurückgegebenen Zeilen über RETURNING mit einem bedingten INSERT verknüpfen. Ein Beispiel: WITH updated AS (UPDATE users SET name = 'John Kane', gender = 'Male', age = 25 WHERE id = 12 RETURNING id) INSERT INTO users (id, name, gender, age) SELECT 12, 'John Kane', 'Male', 25 WHERE NOT EXISTS (SELECT 1 FROM updated);.

Das INSERT wird nur ausgeführt, wenn das UPDATE in dieser Anweisung keine Zeile zurückgegeben hat. Dieses Muster kann nützlich sein, wenn das UPDATE und das ersatzweise INSERT in einer SQL-Anweisung zusammengefasst werden sollen. Bei konkurrierenden Schreibzugriffen ersetzt es jedoch nicht ON CONFLICT. Ein gleichzeitiges INSERT kann weiterhin eine Verletzung des eindeutigen Schlüssels verursachen. Verwenden Sie daher INSERT ... ON CONFLICT, wenn Sie das übliche konfliktfeste UPSERT-Verhalten benötigen.

Mit einer PL/pgSQL-Funktion eine Zeile einfügen oder aktualisieren, falls sie vorhanden ist

Eine PL/pgSQL-Funktion kann die empfohlene Anweisung zur Konfliktbehandlung bündeln, damit sie wiederverwendet werden kann. Wählen Sie Parameternamen, die sich eindeutig von Spaltennamen unterscheiden. Der Funktionsrumpf kann zum Beispiel mit den Parametern p_id integer, p_name text, p_gender text und p_age integer die Anweisung INSERT INTO users (id, name, gender, age) VALUES (p_id, p_name, p_gender, p_age) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; ausführen.

Definieren Sie die Funktion mit CREATE OR REPLACE FUNCTION upsert_user(p_id integer, p_name text, p_gender text, p_age integer) RETURNS void LANGUAGE plpgsql AS $$ BEGIN INSERT INTO users (id, name, gender, age) VALUES (p_id, p_name, p_gender, p_age) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, gender = EXCLUDED.gender, age = EXCLUDED.age; END; $$; und rufen Sie sie anschließend mit SELECT upsert_user(13, 'New User', 'Female', 30); auf. So überlassen Sie dieselbe Behandlung von Konflikten mit eindeutigen Schlüsseln der Datenbank; eine separate UPDATE-then-INSERT-Abfolge und das damit verbundene Wettrennen sind dafür nicht erforderlich.

Fazit

Für den üblichen bedingten Schreibvorgang verwenden Sie INSERT ... ON CONFLICT (UPSERT) mit einem Primärschlüssel oder einer anderen geeigneten eindeutigen Einschränkung als Konfliktziel. Damit wird eine neue Zeile eingefügt oder es werden die von Ihnen ausgewählten Felder aktualisiert, wenn der Schlüssel bereits vorhanden ist. Ein UPDATE mit anschließendem INSERT und eine UPDATE-then-INSERT-CTE können die beiden möglichen Ergebnisse veranschaulichen, bieten aber nicht dieselbe Behandlung konkurrierender Konflikte. Eine PL/pgSQL-Funktion eignet sich, wenn Sie den Vorgang zur Konfliktbehandlung innerhalb der Datenbank wiederverwenden möchten.