PostgreSQL^® 9.5 - Upsert

Unter zahlreichen neuen Features der kommenden PostgreSQL^®-Version 9.5 sticht ein Feature ganz besonders hervor: INSERT ... ON CONFLICT ..., oft einfach auch „UPSERT“ genannt.

In manchen anderen Datenbanksystem bereits seit längerer Zeit verfügbar, bietet PostgresSQL nun ebenfalls die Möglichkeit, UPSERT zu verwenden.

Was ist UPSERT?

Mit Hilfe von UPSERT lassen sich alternative Aktionen beschreiben, die beim Fehlschlagen eines Inserts ausgeführt werden sollen. Mehrere Schritte können in einem einzigen Befehls zusammengefasst werden. Ein Beispiel für solch eine alternative Aktion ist das Ignorieren eventueller Constraint-Fehler oder das Update der bereits vorhandenen Tupel.

Beispiel

Ein Beispiel sagt mehr als 1000 Worte. Das folgende Beispiel soll zeigen, wozu das neue Feature eingesetzt werden kann. Als Basis dient uns hierzu eine Anwendung aus dem Bereich einer Autovermietung.

Die Tabelle „vermietungen“ beinhaltet den aktuellen Status jedes erfassten Autos:

CREATE TABLE vermietungen (
    kennzeichen TEXT    PRIMARY KEY,
    status      TEXT    NOT NULL
)

Als Beispieldaten dienen uns zwei einfache Datensätze:

INSERT INTO vermietungen
    (kennzeichen, status)
    VALUES
        ('MG-CD-5432', 'vermietet'),
        ('MG-CD-6000', 'nicht vermietet');

Nun soll der Status eines neuen Fahrzeugs eingetragen werden. Normalerweise müsste entweder seitens der Anwendung oder durch entsprechende Trigger sichergestellt sein, dass das Fahrzeug bereits in der Tabelle „vermietungen“ vorhanden ist. Erst dann kann der Status entsprechend geändert werden. Es muss also zwischen bereits vorhandenen und noch nicht vorhandenen Fahrzeugen / Datensätzen unterschieden werden. Dies erzeugt eine Reihe von zusätzlichen SQL-Statements. Mit Hilfe von INSERT ... ON CONFLICT ... kann dies vereinfacht werden.

INSERT INTO vermietungen
    (kennzeichen, status)
    VALUES ('MG-CD-5432', 'nicht vermietet')
    ON CONFLICT ON CONSTRAINT vermietungen_pkey DO UPDATE SET status = 'nicht vermietet';
INSERT 0 1

Technische Details

Wie im obigen Beispiel bereits gezeigt, muss nicht nur angegeben werden was ausgeführt wird, sondern auch wann. So ist es möglich bei Verletzung unterschiedlicher Constraints unterschiedliche Aktionen auszuführen.

Ein Auszug aus der PostgreSQL^® 9.5 Dokumentation zeigt die Syntax:

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
 
where conflict_target can be one of:
 
    ( { column_name_index | ( expression_index ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name
 
and conflict_action is one of:
 
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

Im Gegensatz zu zwei separaten Aktionen (INSERT mit nachfolgendem UPDATE) handelt es sich bei INSERT ... ON CONFLICT DO UPDATE ... um eine atomare Aktion. Das bedeutet, dass keine zusätzlichen Locks benötigt werden, um nebenläufige Aktionen zu berücksichtigen.

UPSERT ist nur eines von vielen neuen und interessanten Features, die mit Version 9.5 von PostgreSQL^® eingeführt werden. Ein Übersicht über die neuen Features bietet das Kategorien: PostgreSQL® Tags: PostgreSQL®