Menu

Upsert PostgreSQL avec INSERT ... ON CONFLICT

L'upsert dans PostgreSQL avec INSERT ... ON CONFLICT : DO UPDATE avec EXCLUDED, DO NOTHING, mises à jour conditionnelles, compteurs, et comment distinguer les lignes insérées des lignes mises à jour.

Cette page contient des éditeurs exécutables. Modifiez, exécutez et voyez la sortie instantanément.

PostgreSQL n'a pas de mot-clé UPSERT. On écrit INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. Quand la nouvelle ligne violerait une contrainte unique sur cette colonne, PostgreSQL met à jour la ligne existante à la place. DO NOTHING l'ignore.

A-100 existait déjà, donc son prix est passé à 12.5. B-200 était nouveau et a été inséré. Une seule instruction gère les deux cas, et elle reste correcte quand d'autres sessions insèrent les mêmes clés en même temps.

Insérer ou mettre à jour une ligne

L'instruction a trois parties :

INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
  • La cible du conflit nomme les colonnes d'un index ou d'une contrainte unique, comme la clé primaire. Elle doit correspondre exactement à l'un d'eux, sinon PostgreSQL lève "there is no unique or exclusion constraint matching the ON CONFLICT specification". Vous pouvez nommer la contrainte à la place : ON CONFLICT ON CONSTRAINT products_pkey.
  • EXCLUDED est la ligne que vous avez tenté d'insérer. EXCLUDED.price est le nouveau prix, et products.price le prix déjà stocké.
  • DO UPDATE SET ne modifie que les colonnes listées. Les autres colonnes de la ligne existante gardent leurs valeurs.

Un index unique avec une clause WHERE (un index partiel) peut aussi servir de cible, si vous répétez sa condition : ON CONFLICT (email) WHERE deleted_at IS NULL.

Ignorer les doublons avec DO NOTHING

DO NOTHING insère les lignes nouvelles et écarte celles qui entrent en conflit, sans erreur. Sans cible de conflit, il ignore une ligne qui viole n'importe quelle contrainte unique.

RETURNING ne liste que lin@example.com. Une ligne ignorée n'est pas renvoyée, donc DO NOTHING ... RETURNING montre quelles lignes étaient nouvelles.

Incrémenter un compteur

Pour augmenter une valeur stockée au lieu de la remplacer, ajoutez EXCLUDED à la valeur actuelle. Qualifiez la valeur actuelle avec le nom de la table. Dans DO UPDATE, un views seul correspond à la fois à la table et à EXCLUDED, et PostgreSQL s'arrête avec "column reference "views" is ambiguous".

La première instruction insère la ligne avec 1 et les deux suivantes y ajoutent 1, donc /home finit à 3.

Mettre à jour seulement si la valeur a changé

Un WHERE après DO UPDATE SET décide, ligne par ligne, si la mise à jour a lieu. Quand il est faux, la ligne reste telle quelle et ne compte ni comme insérée ni comme mise à jour. Servez-vous-en pour ne garder que les données les plus récentes, ou pour éviter d'écrire une nouvelle version de ligne quand rien n'a changé.

A-100 garde 10 parce que la ligne entrante est plus ancienne. B-200 prend 25. Pour éviter les mises à jour qui écriraient les mêmes valeurs, utilisez WHERE prices.price IS DISTINCT FROM EXCLUDED.price, qui gère aussi NULL.

Distinguer les lignes insérées des lignes mises à jour

RETURNING fonctionne sur un upsert comme sur n'importe quelle insertion. Pour distinguer les deux cas, lisez la colonne système xmax, qui vaut 0 pour une ligne insérée par cette instruction :

A-100 revient avec 8 et inserted à false, C-300 avec 7 et true. xmax est un détail interne, pas une API documentée, donc considérez-le comme une simple commodité. La page RETURNING couvre la clause en détail.

Les erreurs que vous rencontrerez

  • "ON CONFLICT DO UPDATE command cannot affect row a second time" : la même clé apparaît deux fois dans le VALUES ou le SELECT d'une même instruction. Retirez d'abord les doublons, par exemple avec SELECT DISTINCT ON (sku) ....
  • "column reference is ambiguous" : qualifiez la colonne avec le nom de la table dans les expressions de SET et dans le WHERE, comme plus haut.
  • Trous dans les id identity ou serial : une insertion qui se transforme en mise à jour a déjà pris une valeur à la séquence, donc les id sautent des numéros. C'est normal.

MERGE (PostgreSQL 15 et plus) peut aussi insérer ou mettre à jour, mais il ne protège pas contre deux sessions qui insèrent la même clé en même temps. Pour les upserts par clé, utilisez ON CONFLICT. La référence officielle d'INSERT liste toutes les options.

Questions fréquentes

PostgreSQL a-t-il une instruction UPSERT ?

Il n'existe pas de mot-clé UPSERT. PostgreSQL 9.5 a ajouté INSERT ... ON CONFLICT, qui insère une ligne ou, quand elle violerait une contrainte unique, met à jour la ligne existante (DO UPDATE) ou l'ignore (DO NOTHING).

INSERT ... ON CONFLICT est-il sûr avec des insertions concurrentes ?

Oui. Chaque ligne proposée finit soit insérée, soit mise à jour, même quand d'autres sessions insèrent la même clé au même moment. Un SELECT suivi d'un INSERT ou d'un UPDATE dans le code de l'application n'offre pas cette garantie.

Faut-il utiliser MERGE ou ON CONFLICT pour un upsert ?

Utilisez ON CONFLICT pour insérer ou mettre à jour par clé. MERGE (PostgreSQL 15 et plus) couvre davantage de cas, comme la suppression des lignes qui correspondent. Avec des insertions concurrentes de la même clé, il peut échouer sur une violation d'unicité, ce que ON CONFLICT évite.

Pourquoi j'obtiens "there is no unique or exclusion constraint matching the ON CONFLICT specification" ?

Les colonnes de ON CONFLICT (...) doivent correspondre exactement à un index ou une contrainte unique. Ajoutez une contrainte UNIQUE ou un index unique sur ces colonnes, ou indiquez les colonnes d'un index qui existe déjà.

Illustration des langages de programmation de Coddy

Apprendre à coder avec Coddy

Commencer