Insertions / mises à jour

(sujet préalablement traité avec la version 9.5 puis mis à jour avec la version 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 intègre depuis la 9.5 la fonctionnalité d'UPSERT 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 pour générer un conflit et assurer l'arbitrage.
      INSERT ... ON CONFLICT est une instruction sûre au niveau du A (atomicité) de ACID. Elle est donc adaptée pour une utilisation transactionnelle.

      Depuis la version 11, PostgreSQL intègre également MERGE. Démonstration :

select version(); version ----------------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 11devel (Debian 11~~devel~20180403.1030-1~522.git83454e3.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.3.0-14) 7.3.0, 64-bit (1 ligne) CREATE TABLE t1 (c1 INTEGER, 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 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); ERREUR: la colonne " t1 " de la relation " t1 " n'existe pas LIGNE 2 : WHEN MATCHED THEN UPDATE SET t1.c2 = t2.c2 MERGE INTO t1 USING t2 ON (t1.c1 = t2.c1) WHEN MATCHED THEN UPDATE SET c2 = t2.c2 WHEN NOT MATCHED THEN INSERT (c1, c2) VALUES (t2.c1, t2.c2); MERGE 2 select * from t2; c1 | c2 ----+---- 1 | B 2 | C (2 lignes)


      Dans une utilisation basique, vous pouvez à présent utiliser MERGE sous PostgreSQL comme vous le faites avec Oracle Database. Une contrainte n'est pas nécessaire et la syntaxe est quasi identique. Il faut juste faire attention à ne pas préfixer la colonne à mettre à jour avec le nom de la table sur laquelle s'opère le MERGE dans la clause UPDATE. Le comportement du MERGE de PostgreSQL est à ce niveau cohérent avec celui de sa commande UPDATE et conforme au standard SQL.
      La communauté PostgreSQL a longtemps été réticente à implémenter la commande MERGE. Ces réticences étaient pertinentes et n'ont pas été balayées. MERGE est une instruction plus complexe que les autres instructions DML, notamment dans la gestion de la concurrence. Elle nécessite des tests approfondis et il peut être difficile de l'intégrer dans les programmes transactionnels. Vous pouvez en revanche l'utiliser plus facilement lorsque la concurrence n'est pas un problème, lors d'un chargement en environnement décisionnel par exemple.

      Attention, la version 11 est encore en développement au 03/04/2018. Au vu des discussions en cours, l'implémentation de MERGE va probablement encore faire l'objet de BEAUCOUP de correctifs d'ici la sortie officielle mais il est déjà possible de l'expérimenter.
      Mise à jour du 12/04/2018 : l'introduction de la commande MERGE a finalement été annulée en version 11. Cela illustre parfaitement le processus de qualité mis en oeuvre lors du développement de PostgreSQL. La commande était fonctionnelle et documentée. J'ai pu l'utiliser sur les exemples de cette page sans problème même si elle était moins performante que INSERT .. ON CONFLICT.
      Mais, au contraire de ce qui se passe dans d'autres équipes de développement, "ça fonctionne" n'est pas suffisant pour qu'un code soit accepté dans PostgreSQL. A la suite d'une discussion, la qualité du code de MERGE a été jugée encore insuffisante et la fonctionnalité a été rejetée. Rendez-vous en 12 devel !

Mise à jour : 12/04/2018