Insertions / mises à jour

(sujet mis à jour avec les versions 11 devel et 15 devel)

L’UPSERT : insérer ou mettre à jour en une seule instruction

      Oracle implémente la fonctionnalité d’UPSERT à l’aide de l’instruction MERGE définie par le standard SQL :

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production CREATE TABLE t1 (c1 INTEGER, c2 CHARACTER VARYING(10)); Table created. INSERT INTO t1 VALUES(1, 'A'); 1 row created. CREATE TABLE t2 (c1 INTEGER, c2 CHARACTER VARYING(10)); Table created. INSERT INTO t2 VALUES(1, 'B'); 1 row created. INSERT INTO t2 VALUES(2, 'C'); 1 row created. MERGE INTO t1 USING t2 ON (t1.c1 = t2.c1) WHEN MATCHED THEN UPDATE SET t1.c2 = t2.c2 WHEN NOT MATCHED THEN INSERT (c1, c2) VALUES (t2.c1, t2.c2); 2 rows merged. COMMIT; Commit complete. SELECT * FROM t1; C1 C2 ---------- ---------- 1 B 2 C

      PostgreSQL n’implémente pas MERGE. C’est un choix délibéré : la communauté a notamment jugé que le standard SQL n’était pas suffisamment explicite au niveau de la gestion de la concurrence. Cependant, depuis PostgreSQL 9.5, la fonctionnalité d’UPSERT est implémentée gràce à une extension propriétaire à l’instruction INSERT, la clause ON CONFLICT :

select version(); version ------------------------------------------------------------- PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 64-bit (1 ligne) CREATE TABLE t1 (c1 INTEGER primary key, c2 CHARACTER VARYING(10)); CREATE TABLE INSERT INTO t1 VALUES(1, 'A'); INSERT 0 1 CREATE TABLE t2 (c1 INTEGER, c2 CHARACTER VARYING(10)); CREATE TABLE INSERT INTO t2 VALUES(1, 'B'); INSERT 0 1 INSERT INTO t2 VALUES(2, 'C'); INSERT 0 1 INSERT INTO t1(c1, c2) (SELECT c1, c2 FROM t2) ON CONFLICT(c1) DO UPDATE SET c2 = EXCLUDED.c2; INSERT 0 2 SELECT * FROM t1; c1 | c2 ----+---- 1 | B 2 | C (2 lignes)

      Le mot clé EXCLUDED est un alias désignant les lignes proposées à l'insertion. Vous pouvez l'utiliser dans une clause DO UPDATE pour réaliser une mise à jour de tout ou partie des colonnes pour la ligne en conflit. Il serait aussi possible de ne rien faire en cas de conflit (DO NOTHING).
      A noter qu’une contrainte d’unicité, ici la clé primaire définie au niveau de c1 pour t1, est nécessaire avec PostgreSQL pour générer un conflit et assurer l’arbitrage au niveau de l’UPSERT. Ce n’était pas nécessaire avec MERGE sous Oracle.
      Ce que permet MERGE dépasse la notion d’UPSERT. L’implémentation d’Oracle permet de détruire les lignes plutôt que les mettre à jour (un DELUPSERT ?). Je ne suis généralement pas favorable aux extensions propriétaires lorsque des solutions définies par le standard existent. Mais, si vous voulez absolument utiliser l’UPSERT avec PostgreSQL, il est intéressant de savoir que c’est malgré tout possible. Utiliser la clause ON CONFLICT est préférable à une solution basée sur PL/pgSQL ou autre.

Mise à jour : 20/02/2018