Documentation PostgreSQL 15.15 » Référence » Commandes SQL » 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 [...]

    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 [ AND condition ] THEN { merge_insert | DO NOTHING } }

    et merge_insert est :

        INSERT [( nom_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` ou `NOT MATCHED` 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.

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 cible où fusionner des informations. Si `ONLY` est indiqué avant le nom de la 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.

`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.

`clause_when`

  Au moins une clause `WHEN` est nécessaire.

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

  À l'inverse, si la clause `WHEN` indique `WHEN NOT MATCHED`, et que la ligne candidate n'a pas de ligne correspondante dans la table cible, et si `condition` est absente ou évaluée à `true`, alors la clause `WHEN` est exécutée.

`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` peut se référer à des colonnes de la relation source comme de la cible. Une condition d'une clause `WHEN NOT MATCHED` ne peut se référer qu'à des colonnes de la relation source, puisque, par définition, il n'y a pas de ligne correspondante dans la cible. Seuls les attributs système 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`. Dans une clause `WHEN NOT MATCHED`, l'expression peut utiliser des valeurs de `source_des_données`.

`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. La sous-requête peut faire référence aux valeurs de la ligne originale dans la table cible, et aux valeurs provenant de la ligne `source_de_donnees`.

## 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.

## Notes

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

1. Exécute tous les triggers `BEFORE STATEMENT` de chaque action spécifiée, que leur clause `WHEN` renvoie quelque chose ou non.

2. 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`).

   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.

3. 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` ou `NOT MATCHED`). 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.

Il n'y a pas de clause `RETURNING` à une commande `MERGE`. Les action `INSERT`, `UPDATE` et `DELETE` ne peuvent contenir de clauses `RETURNING` ou `WITH`.

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.

    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;

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

## Compatibilité

Cette commande se conforme au standard SQL.

La clause `WITH` et l'action `DO NOTHING` sont des extensions au standard SQL.