Insertions / mises à jour

(sujet préalablement traité avec les versions 9.5 et 11devel)

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 15, PostgreSQL intègre également MERGE. Démonstration :

select version(); version ------------------------------------------------------------------------------------------------------------------------------------------------------------------ PostgreSQL 15devel (Debian 15~~devel~20220329.1031-1~680.git8cd7627.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 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, délicate à manipuler au niveau de la concurrence transactionnelle. Cependant, cette commande fait partie de la norme SQL et il était important de l'implémenter. C'est à présent chose faite et vous pouvez l'utiliser dans vos programmes transactionnels et décisionnels, dès maintenant en test, puis en production dès la sortie officielle de PostgreSQL 15 prévue avant la fin de l'année 2022.

Mise à jour : 28/03/2022