LUPSERT : insérer ou mettre à jour en une seule instruction
Oracle implémente la fonctionnalité dUPSERT à laide de linstruction MERGE définie par le standard SQL :
PostgreSQL intègre depuis la 9.5 la fonctionnalité d'UPSERT gràce à une extension propriétaire à l'instruction INSERT, la clause ON CONFLICT :
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 :
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