UPSERT mit ON CONFLICT

Grundform

Voraussetzung: in der Tabelle gibt es einen UNIQUE-Constraint auf der Spalte, über die du den Konflikt erkennst. Hier z. B. email UNIQUE in der users-Tabelle. Damit weiß Postgres: wenn jemand einen User mit derselben E-Mail einfügen will, ist das ein Konflikt — und genau dann soll DO UPDATE greifen.

INSERT INTO users (email, name, last_seen_at)
VALUES ('alice@example.com', 'Alice', now())
ON CONFLICT (email) DO UPDATE
    SET name = EXCLUDED.name,
        last_seen_at = EXCLUDED.last_seen_at;

Was hier Schritt für Schritt passiert:

1. Postgres versucht den INSERT. Wenn alice@example.com noch nicht in der Tabelle ist, wird die neue Zeile angelegt — fertig.
2. Wenn die E-Mail aber bereits existiert, würde der UNIQUE-Constraint normalerweise einen Fehler auslösen. Mit ON CONFLICT (email) fängt Postgres das ab und führt stattdessen den DO UPDATE-Teil aus.
3. Im DO UPDATE-Teil ist EXCLUDED eine Pseudo-Tabelle mit den Werten, die wir gerade einfügen wollten. EXCLUDED.name ist also 'Alice', EXCLUDED.last_seen_at ist der now()-Zeitstempel. Damit überschreiben wir die bereits existierenden Felder.

Das Ergebnis: die Zeile existiert garantiert mit den aktuellen Werten — egal, ob sie vorher schon da war oder nicht. „Insert if new, update if exists” — daher der Name Upsert.

DO NOTHING ist die andere Option: Konflikt → leise ignorieren.

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

Praktisch beim Bulk-Import, wenn man nur neue Zeilen will und Duplikate egal sind.

Constraint-Identifikation

ON CONFLICT (email) adressiert den UNIQUE-Constraint über die Conflict-Target-Spalten. Drei Schreibweisen sind möglich:

-- Variante 1: Spaltenliste (Postgres findet den passenden UNIQUE/PK)
ON CONFLICT (email)

-- Variante 2: explizit per Constraint-Name
ON CONFLICT ON CONSTRAINT users_email_key

-- Variante 3: bei zusammengesetztem Constraint
ON CONFLICT (tenant_id, email)

Es muss ein UNIQUE oder PRIMARY KEY-Constraint existieren, der genau diese Spalten umfasst. Ohne Constraint scheitert das Statement mit „there is no unique or exclusion constraint matching the ON CONFLICT specification”.

EXCLUDED — die Pseudo-Tabelle

EXCLUDED.name ist nicht der alte Wert in der Tabelle, sondern der Wert, der gerade eingefügt werden sollte. Praktisch:

INSERT INTO users (email, login_count)
VALUES ('alice@example.com', 1)
ON CONFLICT (email) DO UPDATE
    SET login_count = users.login_count + 1;

Hier siehst du den Unterschied: users.login_count ist der alte Wert in der Tabelle, EXCLUDED.login_count wäre 1 (was eingefügt werden sollte). Bei einem Login-Counter willst du den alten Wert plus eins — also users.login_count + 1.

Beide sind in DO UPDATE verfügbar.

Mit WHERE — bedingter Update

Der Update-Teil kann eine WHERE-Klausel haben:

INSERT INTO users (email, name, updated_at)
VALUES ('alice@example.com', 'Alice', now())
ON CONFLICT (email) DO UPDATE
    SET name = EXCLUDED.name,
        updated_at = EXCLUDED.updated_at
WHERE users.updated_at < EXCLUDED.updated_at;

Wenn der Insert kommt, der schon „älter” ist als das, was in der Tabelle steht, passiert nichts — Race-Condition-Schutz bei verteilten Schreibern.

Bulk-UPSERT

Der häufigste reale Use-Case: viele Zeilen auf einmal upserten — etwa beim Sync mit einer externen Quelle.

INSERT INTO users (email, name) VALUES
    ('alice@example.com', 'Alice'),
    ('bob@example.com',   'Bob'),
    ('carol@example.com', 'Carol')
ON CONFLICT (email) DO UPDATE
    SET name = EXCLUDED.name;

Das Statement ist atomar — entweder werden alle Zeilen erfolgreich verarbeitet oder keine. Bei sehr großen Bulk-Operationen (>10.000 Zeilen) lohnen sich aber Chunks: pro Chunk eine Transaktion, sonst werden Locks und WAL zu groß.

Mit RETURNING

Sehr nützlich, um nach einem UPSERT die finale Zeile zurückzubekommen:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE
    SET name = EXCLUDED.name
RETURNING id, email, name,
          (xmax = 0) AS was_inserted;

xmax = 0 zeigt: war es ein neuer Insert oder ein Update? Ohne separate Query.

DO NOTHING mit RETURNING hat eine Falle: bei einem Konflikt fällt auch das RETURNING aus — du bekommst keine Zeile zurück. Wenn du in dem Fall trotzdem die existierende Zeile brauchst, hilft ein CTE-Pattern:

WITH ins AS (
    INSERT INTO users (email, name)
    VALUES ('alice@example.com', 'Alice')
    ON CONFLICT (email) DO NOTHING
    RETURNING *
)
SELECT * FROM ins
UNION ALL
SELECT * FROM users WHERE email = 'alice@example.com'
                      AND NOT EXISTS (SELECT 1 FROM ins);

Etwas länglich — aber das ist die saubere Variante, wenn man immer eine Zeile zurückhaben will, egal ob sie neu war oder schon existierte.

Praxis-Beispiele

Counter-Pattern: Login-Zähler hochzählen

INSERT INTO user_stats (user_id, login_count, last_login_at)
VALUES ($1, 1, now())
ON CONFLICT (user_id) DO UPDATE
    SET login_count = user_stats.login_count + 1,
        last_login_at = EXCLUDED.last_login_at;

user_stats.login_count + 1 greift den vorhandenen Wert und inkrementiert. EXCLUDED.last_login_at ist der gerade übergebene now()-Zeitstempel. Atomar und race-condition-frei — ohne explizite Transaktion.

Bulk-UPSERT aus externer Quelle

myapp=> INSERT INTO products (sku, name, price, updated_at) VALUES
        ('A1', 'Widget',    9.95, now()),
        ('B2', 'Gadget',   19.95, now()),
        ('C3', 'Gizmo',    29.95, now())
        ON CONFLICT (sku) DO UPDATE
            SET name = EXCLUDED.name,
                price = EXCLUDED.price,
                updated_at = EXCLUDED.updated_at
        WHERE products.updated_at < EXCLUDED.updated_at
        RETURNING sku, (xmax = 0) AS was_inserted;

 sku | was_inserted
-----+--------------
 A1  | t
 B2  | f
 C3  | t

Drei Ergebnisse: A1 und C3 waren neu (t = inserted), B2 wurde aktualisiert. Das WHERE products.updated_at < EXCLUDED.updated_at schützt davor, dass ein verspäteter Feed einen aktuelleren Datensatz überschreibt.

Race-Condition-Schutz für Initialisierung

INSERT INTO user_settings (user_id, theme, language)
VALUES ($1, 'dark', 'de')
ON CONFLICT (user_id) DO NOTHING
RETURNING id;

Klassischer „get-or-create”-Fall, multithread-sicher: zwei parallele Calls können nicht beide die Default-Zeile anlegen. Bei Konflikt liefert RETURNING zwar nichts zurück — aber die Zeile existiert garantiert.

Weiterführende Ressourcen

Externe Quellen

• INSERT … ON CONFLICT – PostgreSQL Documentation
• Upsert in PostgreSQL – PostgreSQL Wiki
• MERGE – PostgreSQL Documentation
• Release Notes 9.5 – ON CONFLICT eingeführt

Verwandte Artikel

• INSERT, UPDATE, DELETE
• RETURNING-Klausel
• MERGE-Statement
• COPY für Bulk-Import