MERGE

» » »

Précédent	Niveau supérieur	Suivant
LOCK	Sommaire	MOVE

MERGE

MERGE — Insère, met à jour ou efface des lignes d'une table selon une condition

Synopsis

[ WITH clause_with [, ...] ]
MERGE INTO [ ONLY ] nom_table_cible [ * ] [ [ AS ] alias_cible ]
USING source_des_données ON condition_de_jointure
clause_when [...]
[ RETURNING { * | expression_en_sortie [ [ AS ] nom_sortie ] } [, ...] ]

où source_des_données est :

{ [ ONLY ] nom_table_source [ * ] | ( requête_source ) } [ [ AS ] alias_source ]

et clause_when est :

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN { merge_insert | DO NOTHING } }

et merge_insert est :

INSERT [( nom_de_colonne [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

et merge_update est :

UPDATE SET { nom_de_colonne = { expression | DEFAULT } |
             ( nom_de_colonne [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
             ( nom_de_colonne [, ...] ) = ( sous-SELECT )

et merge_delete est :

DELETE

Description

MERGE exécute des actions qui modifient des lignes dans la table cible identifiée par nom_table_cible, à partir d'une source_des_données. MERGE est un ordre SQL qui, à lui seul et selon la condition, peut opérer un INSERT, un UPDATE ou un DELETE sur des lignes, une tâche qui nécessiterait sinon de multiples appels en langage procédural.

Tout d'abord, la commande MERGE fait une jointure entre source_des_données et la table cible, ce qui donne zéro ou plus ligne(s) candidates au changement. Pour chacune, on définit le statut MATCHED, NOT MATCHED BY SOURCE ou NOT MATCHED BY TARGET une fois pour toutes ; puis les clauses WHEN sont évaluées dans l'ordre spécifié. Pour chaque ligne candidate à un changement, la première clause évaluée à true est exécutée. Au plus une clause WHEN est exécutée pour chaque ligne candidate au changement.

Les opérations de MERGE ont le même effet que les commandes UPDATE, INSERT, ou DELETE de même nom. La syntaxe de ces commandes diffère, notamment sur l'absence de clause WHERE et de nom de table. Toutes les actions opèrent sur la table cible, bien que des modifications sur d'autres tables puissent être faites en passant par des triggers.

Si DO NOTHING est précisé, la ligne source est passée. Comme les actions sont évaluées dans l'ordre spécifié, il peut être pratique de sauter les lignes source sans intérêt avant une gestion plus fine.

La clause optionnelle RETURNING fait que MERGE doit calculer et renvoyer les valeurs en se basant sur chaque ligne insérée, mise à jour ou supprimée. Toute expression utilisant les colonnes de la table source ou cible ou la fonction merge_action() peut être calculée. Quand une action INSERT ou UPDATE est réalisée, les nouvelles valeurs des colonnes de la table cible sont utilisées. Quand un DELETE est réalisé, les anciennes valeurs des colonnes de la table cible sont utilisées. La syntaxe de la liste RETURNING est identique à celle de la liste en sortie de SELECT.

Il n'y a pas de droit MERGE dédié. Si vous spécifiez une mise à jour, vous devez posséder le droit UPDATE sur les colonnes de la table cible mentionnées dans la clause SET. Pour une insertion, vous devez posséder le droit INSERT sur la table cible. Et pour une suppression, vous devez posséder le droit DELETE sur la table cible. Si vous ajoutez une action DO NOTHING, vous devrez avoir le droit SELECT sur au moins une colonne de la table cible. Vous aurez aussi besoin du droit SELECT sur toute colonne de source_des_données et sur toute colonne de la table cible référencée dans toute condition (incluant condition_jointure) ou expression. Les droits sont testés et vérifiés une fois au démarrage de l'ordre, que les clauses WHEN concernées soient exécutées ou pas.

MERGE n'est pas supporté si la table cible est une vue matérialisée, une table étrangère, ou si des règles y ont été posées.

Paramètres

requête_with

La clause WITH vous permet d'ajouter une ou plusieurs sous-requêtes pouvant être référencées dans la requête MERGE. Voir Section 7.8 et SELECT pour des détails. Notez que WITH RECURSIVE n'est pas supporté par MERGE.

nom_table_cible

Nom (éventuellement qualifié du schéma) de la table ou vue cible où fusionner des informations. Si ONLY est indiqué avant un nom de table, les lignes correspondantes sont mises à jour ou supprimées uniquement dans la table nommée. Si ONLY n'est pas indiquée, les lignes correspondantes sont aussi mises à jour ou supprimées des tables héritant de la table nommée. * peut être indiquée en option après le nom de la table pour indiquer explicitement que les tables enfants sont inclus. Le mot clé ONLY et l'option * n'affectent pas les actions d'insertion, qui insèrent toujours uniquement dans la table nommée.

Si nom_table_cible est une vue, elle doit être dans la capacité d'être mise à jour automatiquement sans trigger INSTEAD OF ou elle doit avoir des triggers INSTEAD OF pour chaque type d'action (INSERT, UPDATE et DELETE) indiqué dans les clauses WHEN. Les vues avec des règles ne sont pas acceptées.

alias_cible

Un nom à substituer à la table cible. Quand un alias est fourni, il cache complètement le nom réel de la table. Par exemple, avec MERGE INTO foo AS f, le reste de l'ordre MERGE devra appeler la table avec f et non foo.

nom_table_source

Le nom (éventuellement qualifié du schéma) de la table, vue ou table de transition source. Si ONLY est précisé avant le nom de la table, les lignes correspondantes sont inclus uniquement à partir de la table nommée. Si ONLY n'est pas indiqué, les lignes correspondantes sont aussi incluses à partir des tables héritant de la table nommée. En option, * peut être ajouté après le nom de la table pour indiquer explicitement que les tables enfants sont incluses.

requête_source

Une requête (commande SELECT ou VALUES) fournissant les lignes à fusionner dans la table cible. Se référer à la documentation de SELECT ou VALUES pour une description des syntaxes.

alias_source

Un nom substitut de la source de données. Quand un alias est fourni, il cache complètement le nom réel de la table ou le fait qu'une requête ait été fournie.

condition_de_jointure

condition_de_jointure est une expression dont le résultat est un boolean (similaire à une clause WHERE), lequel indique quelles lignes de source_des_données correspondent à des lignes dans la table cible.

Avertissement

Seules les colonnes de la table cible que l'on tente de rapprocher de source_des_données devraient apparaître dans condition_de_jointure. Les sous-expressions de la condition qui ne référencent que la table cible peuvent affecter le choix de l'action, souvent de manière surprenante.

Si les clauses WHEN NOT MATCHED BY SOURCE et WHEN NOT MATCHED [BY TARGET] sont toutes les deux indiquées, la commande MERGE réalisera une jointure FULL entre source_donnees et la table cible. Pour que cela fonctionne, au moins une sous-expression condition_jointure doit utiliser un opérateur qui peut accepter une jointure par hachage, ou toutes les sous-expressions doivent utiliser des opérateurs qui peuvent accepter une jointure par assemblage.

clause_when

Au moins une clause WHEN est nécessaire.

La clause WHEN peut préciser WHEN MATCHED, WHEN NOT MATCHED BY SOURCE ou WHEN NOT MATCHED [BY TARGET]. Notez que le standard SQL définit seulement WHEN MATCHED et WHEN NOT MATCHED(qui est défini comme signifiant qu'aucune ligne cible ne correspond). WHEN NOT MATCHED BY SOURCE est une extension du standard SQL, tout comme l'option d'ajouter BY TARGET à WHEN NOT MATCHED, pour rendre sa signification plus explicite.

Si la clause WHEN indique WHEN MATCHED et que la ligne candidate correspond à une ligne de source_donnees vers une ligne dans la table cible, et si condition est absente ou évaluée à true, alors la clause WHEN est exécutée.

Si la clause WHEN indique WHEN NOT MATCHED BY SOURCE et que la ligne candidate représente une ligne dans la table cible qui ne correspond pas à une ligne dans source_donnees, la clause WHEN est exécutée si condition est absente ou est évaluée à true.

Si la clause WHEN indique WHEN NOT MATCHED [BY TARGET] et que la ligne candidate représente une ligne dans source_donnees qui ne correspond pas à une ligne dans la table cible, la clause WHEN est exécutée si la condition est absente ou est évaluée à true.

condition

Une expression dont le résultat est un boolean. Si cette expression renvoie true, alors la clause WHEN qu'elle accompagne est exécutée pour la ligne en cours.

Une condition d'une clause WHEN MATCHED BY SOURCE peut seulement faire référence aux colonnes de la relation cible car, par définition, il n'existe pas de ligne source correspondante. Une condition sur une clause WHEN NOT MATCHED[BY TARGET] peut seulement faire référence aux colonnes de la ration source puisque, par définition, il n'existe pas de ligne cible correspondante. Seuls les attributs du système provenant de la table cible sont accessibles.

merge_insert

Spécification de la clause INSERT qui insère une ou plusieurs lignes dans la table cible. Les noms des colonnes cibles peuvent être listés dans n'importe quel ordre. Si aucune liste n'est donnée, le défaut est l'ensemble des colonnes de la table dans leur ordre de déclaration.

Chaque colonne absente de la liste des champs, explicite ou implicite, sera remplie par une valeur par défaut, soit sa valeur par défaut déclarée, soit NULL.

Si la table cible est une table partitionnée, chaque ligne est routée vers la partition appropriée et insérée dedans. Si la table cible est une partition, une erreur se produira si une ligne quelconque viole la contrainte de partitionnement.

Les noms de colonnes ne peuvent être spécifiés qu'une fois. Les commandes INSERT ne peuvent pas contenir des sous-SELECT.

Une seule clause VALUES peut être spécifiée. Elle ne peut se référer qu'à des colonnes de la table source, puisque par définition il n'y a pas de ligne cible correspondante.

merge_update

Spécification de la commande UPDATE qui met à jour la ligne courante de la table cible. Les noms de colonnes ne peuvent être utilisés plus d'une fois.

Ne sont permis ni un nom de table, ni une clause WHERE.

merge_delete

Spécifie une action DELETE qui efface la ligne courante de la table cible. N'incluez ni le nom de la table ni aucune autre clause, comme vous le feriez pour une commande DELETE.

nom_de_colonne

Le nom d'une colonne dans la table cible. Au besoin, il peut être complété avec un nom de sous-champ ou un indice de tableau. (N'insérer que dans certains champs d'une colonne composée laisse les autres champs à NULL). N'incluez pas le nom de la table dans la définition d'une colonne cible.

OVERRIDING SYSTEM VALUE

Sans cette clause, une erreur sera levée si une valeur explicite (autre que DEFAULT) est spécifiée pour une colonne identité définie comme GENERATED ALWAYS. Cette clause passe outre à la restriction.

OVERRIDING USER VALUE

Avec cette clause, toutes les valeurs fournies pour des colonnes identité définies comme GENERATED BY DEFAULT sont ignorées, et les valeurs générées par séquence sont appliquées.

DEFAULT VALUES

Toutes les colonnes seront remplies avec leur valeur par défaut. (Une clause OVERRIDING n'est alors pas permise.)

expression

Une expression à assigner à une colonne. Au sein d'une clause WHEN MATCHED, elle peut utiliser des valeurs de la ligne originale dans la table cible, et des valeurs de la ligne issue de source_des_données. Si elle est utilisée dans une clause WHEN NOT MATCHED BY SOURCE, l'expression peut seulement utiliser des valeurs provenant de la ligne originale dans la table cible. Si utilisé dans une clause WHEN NOT MATCHED [BY TARGET], l'expression peut seulement utiliser des valeurs provenant de la ligne de source_donnees.

DEFAULT

Positionne une colonne à sa valeur par défaut (qui sera NULL si aucune n'a été définie).

sous-SELECT

Une sous-requête SELECT qui produit autant de colonnes en sortie que celles listées dans la liste de colonnes entre parenthèses la précédent. La sous-requête ne doit pas renvoyer plus d'une ligne quand elle est exécutée. Si elle renvoie une ligne, la valeur de ses colonnes est affectée aux colonnes cibles ; si elle ne renvoie aucune ligne, des valeurs NULL sont affectées aux colonnes cibles. Si utilisé dans une clauseWHEN MATCHED, la sous-requête peut faire référence aux valeurs de la ligne d'origine dans la table cible, et les valeurs provenant de la ligne de source_donnees. Si utilisé dans une clause WHEN NOT MATCHED BY SOURCE, la sous-requête peut seulement faire référence aux valeurs provenant de la ligne d'origine dans la table cible.

expression_en_sortie

Une expression à calculer et renvoyer par la commande MERGE après chaque ligne modifiée (qu'elle soit insérée, mise à jour ou supprimée). L'expression peut utiliser toute colonne des tables source et cible, ou la fonction merge_action() pour renvoyer des informations supplémentaires sur l'action exécutée.

Écrire * renverra toutes les colonnes de la table source, suivi par toutes les colonnes de la table cible. Souvent, cela peut amener beaucoup de duplication car il est commun que les tables source et cible ont un ensemble de colonnes communes. Ceci peut être évité en qualifiant * avec le nom ou l'alias de la table source ou de la table cible.

nom_sortie

Un nom à utiliser pour une colonne renvoyée.

Résultats

Si elle termine avec succès, la commande MERGE renvoie un résultat de la forme :

MERGE nombre_total

nombre_total est le nombre total des lignes changées (qu'elles aient été insérées, mises à jour ou effacées). Si nombre_total vaut 0, aucune ligne n'a été changée de quelque manière que ce soit.

Si la commande MERGE contient une clause RETURNING, le résultat sera similaire à celui d'une requête SELECT contenant les colonnes et valeurs définies dans la liste RETURNING, calculées à partir des lignes insérées, mises à jour ou supprimées par la commande.

Notes

Les étapes suivantes se déroulent pendant l'exécution de MERGE.

Exécute tous les triggers BEFORE STATEMENT de chaque action spécifiée, que leur clause WHEN renvoie quelque chose ou non.
Fait une jointure entre la source et la table cible. La requête résultante sera optimisée comme à l'habitude, et produira un ensemble de lignes candidates au changement. Pour chacune :
1. Évalue pour chaque ligne si elle a une correspondance (MATCHED) ou pas (NOT MATCHED BY SOURCE ou NOT MATCHED [BY TARGET]).
2. Teste chaque condition WHEN dans l'ordre spécifié jusqu'à ce qu'une renvoie true.
3. Quand une condition renvoie true, exécute les actions suivantes :
  1. exécute tous les triggers BEFORE ROW qui se déclenchent sur ce type d'action ;
  2. exécute l'action spécifiée, en vérifiant toutes les contraintes sur la table cible ;
  3. exécute tous les triggers AFTER ROW qui se déclenchent sur ce type d'action.
  Si la relation cible est une vue avec des triggers INSTEAD OF ROW pour le type d'événement de l'action, ils sont utilisés pour réaliser l'action.
Exécute tous les triggers AFTER STATEMENT pour les actions spécifiées, qu'elles se soient ou non réellement produites. C'est équivalent à un ordre UPDATE qui ne modifie aucune ligne.

En résumé, les triggers de niveau instruction (par exemple INSERT) seront déclenchés à chaque spécification d'une action de leur genre. Par contre, les triggers de niveau ligne se déclencheront uniquement pour les types d'événements exécutés. Ainsi un ordre MERGE peut déclencher à la fois des triggers sur instruction UPDATE et sur INSERT, même si seuls des triggers de ligne sur UPDATE sont déclenchés.

Vous devez vous assurer que la jointure produit au plus une ligne candidate pour chaque ligne de la source. En d'autres termes, une ligne cible ne devrait pas être jointe à plus d'une ligne de données source. Si cela devait arriver, une seule ligne parmi les candidates sera utilisée pour modifier la ligne cible ; une erreur sera levée lors des tentatives suivantes de modification de la ligne. Cela peut arriver si des triggers sur ligne modifient la table cible et que les lignes modifiées sont par la suite aussi modifiées par MERGE. Si l'action répétée est un INSERT, cela provoquera une violation d'unicité, alors que des ordres UPDATE ou DELETE répétés causeront une violation de cardinalité ; ce dernier comportement est exigé par le standard SQL. Cela diffère du comportement historique de PostgreSQL dans les jointures au sein d'ordres UPDATE et DELETE, où le deuxième essai de modification d'une même ligne est simplement ignoré, tout comme les suivants.

Si une clause WHEN omet une sous-clause AND, elle devient la clause finale atteinte pour ce type (MATCHED, NOT MATCHED BY SOURCE ou NOT MATCHED [BY TARGET]). Si une autre clause WHEN est spécifiée pour le même type, elle serait probablement inatteignable et une erreur est levée. Si aucune clause finale n'est spécifiée pour un type, il est possible qu'aucune action ne soit exécutée pour la ligne candidate.

L'ordre dans lequel les lignes seront générées depuis la source des données est par défaut indéterminé. Au besoin, une requête_source peut être utilisée pour définir un ordre cohérent, qui pourrait être nécessaire pour éviter des verrous mutuels entre transactions simultanées.

Quand MERGE est lancé en parallèle à d'autres commandes qui modifient la table cible, les règles usuelles d'isolation des transactions s'appliquent ; voir Section 13.2 pour une explication du comportement de chaque niveau d'isolation. Comme alternative permettant de lancer un ordre UPDATE en même temps que se déroule un INSERT, vous pouvez considérer INSERT ... ON CONFLICT. Il y a beaucoup de différences entre les deux ordres, et leurs limites diffèrent, si bien qu'ils ne sont pas interchangeables.

Exemples

Effectue une maintenance sur customer_accounts à partir de nouvelles recent_transactions.

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

Notez que c'est exactement la même chose que l'ordre suivant, car le résultat du MATCHED ne change pas pendant l'exécution.

MERGE INTO customer_account ca
USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

Tente d'insérer un nouvel article dans un stock avec sa quantité. Si l'article existe déjà, met plutôt à jour son stock existant. Interdit les entrées avec un stock à zéro. Renvoie des détails sur les modifications effectuées.

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE
RETURNING merge_action(), w.*;

La table wine_stock_changes peut être, par exemple, une table temporaire récemment chargée dans la base.

Mettre à jour wines en se basant sur une liste de remplacement de lignes pour tout nouveau stock, modifiant les entrées du stock déjà présentes, et supprimant celles qui ne sont plus présentes dans la nouvelle liste.

MERGE INTO wines w
USING new_wine_list s
ON s.winename = w.winename
WHEN NOT MATCHED BY TARGET THEN
  INSERT VALUES(s.winename, s.stock)
WHEN MATCHED AND w.stock != s.stock THEN
  UPDATE SET stock = s.stock
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

Compatibilité

Cette commande se conforme au standard SQL.

La clause WITH, les qualifieurs BY SOURCE et BY TARGET pour WHEN NOT MATCHED, l'action DO NOTHING et la clause RETURNING sont des extensions du standard SQL.