Upsert PostgreSQL : ON CONFLICT, clés et RETURNING

Un upsert PostgreSQL utilise INSERT ... ON CONFLICT pour insérer une ligne ou effectuer une action définie lorsqu’une clé unique existe déjà. Choisissez DO NOTHING pour conserver la ligne existante, ou DO UPDATE pour la modifier. La cible du conflit doit correspondre à l’identité que vous souhaitez reconnaître.

Ce guide utilise une petite table de stock pour montrer ces choix : révisions plus récentes, mises à jour ignorées, clés répétées dans un lot et nouvelles tentatives qui ajoutent deux fois une quantité. Nous avons exécuté 20 contrôles avec PostgreSQL 18.6 dans une nouvelle base locale. Les données sont fictives ; aucune base de production n’a été utilisée.

Définir la clé unique avant d’écrire l’upsert

Dans cet exemple, un SKU identifie un article au sein d’un tenant. Le même SKU peut exister pour un autre tenant ; la clé primaire comprend donc tenant_id et sku. La quantité doit être positive ou nulle, et les révisions commencent à 1.

Exécutez les exemples dans l’ordre, dans une base jetable, avec l’autocommit de psql activé. Arrêtez-vous si le schéma upsert_lab existe déjà. Notre guide de l’hébergement PostgreSQL, en anglais présente le choix entre service géré et base administrée par vos soins ; ce laboratoire utilise un serveur local isolé.

CREATE SCHEMA upsert_lab;
CREATE TABLE upsert_lab.stock (
    tenant_id integer NOT NULL,
    sku text NOT NULL,
    quantity integer NOT NULL CHECK (quantity >= 0),
    revision integer NOT NULL CHECK (revision > 0),
    PRIMARY KEY (tenant_id, sku)
);
INSERT INTO upsert_lab.stock VALUES (7, 'cable', 10, 1);

L’enregistrement initial du câble contient une quantité de 10 à la révision 1. Notre test a également inséré ce SKU pour le tenant 8. Selon cette clé, il s’agit d’un autre enregistrement, et non d’un doublon du câble du tenant 7.

La documentation des contraintes PostgreSQL explique les clés uniques composites et les clés primaires. Un index ordinaire non unique ne remplace pas la règle d’unicité. Dans notre test, ON CONFLICT (sku) a échoué, car cette table ne possède pas de clé unique sur le seul SKU.

Utiliser DO NOTHING pour conserver la ligne existante

INSERT INTO upsert_lab.stock (tenant_id, sku, quantity, revision)
VALUES (7, 'cable', 99, 2)
ON CONFLICT (tenant_id, sku) DO NOTHING
RETURNING tenant_id, sku, quantity, revision;

Cette instruction n’a renvoyé aucune ligne. La quantité stockée est restée à 10, avec la révision 1. La quantité proposée de 99 a été écartée. La clause RETURNING n’a pas récupéré l’enregistrement existant.

Ce comportement convient lorsque la règle consiste à conserver le premier enregistrement stocké. Un résultat vide signifie que cette instruction n’a renvoyé aucune nouvelle ligne insérée. Si l’application a besoin de l’enregistrement courant, récupérez-le explicitement en tenant compte de l’isolation de la transaction et des modifications concurrentes.

Remplacer les valeurs uniquement avec une révision plus récente

Ici, la source attribue des numéros de révision croissants pour chaque combinaison tenant et SKU. C’est une hypothèse explicite de l’exemple. La base ne sait pas si le numéro fourni par la source est fiable.

INSERT INTO upsert_lab.stock AS current_stock
    (tenant_id, sku, quantity, revision)
VALUES (7, 'cable', 15, 2)
ON CONFLICT (tenant_id, sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    revision = EXCLUDED.revision
WHERE EXCLUDED.revision > current_stock.revision
RETURNING tenant_id, sku, quantity, revision;

Colonnes renvoyées, dans le format non aligné de psql :

7|cable|15|2

current_stock désigne la ligne existante. EXCLUDED fournit la ligne proposée : EXCLUDED.quantity vaut donc 15 et EXCLUDED.revision vaut 2. La condition WHERE compare cette révision à celle déjà stockée avant d’autoriser la mise à jour.

Notre contrôle a confirmé que 15 remplace 10. Cette instruction n’ajoute pas 15 à la quantité existante. Répéter exactement l’instruction n’a renvoyé aucune ligne et a laissé la révision 2 inchangée. Envoyer la même révision avec des valeurs différentes serait également ignoré : détectez et résolvez cette incohérence de la source au lieu de supposer que les données ont été acceptées.

La référence INSERT décrit la cible du conflit, la ligne spéciale EXCLUDED et la condition de mise à jour. Si vos données n’ont pas de révision fiable, choisissez une autre règle explicite plutôt que d’ajouter ce champ uniquement pour reproduire l’exemple.

Vérifier les révisions anciennes et les nouveaux enregistrements

Après la mise à jour réussie, essayez une ancienne révision du câble :

INSERT INTO upsert_lab.stock AS current_stock
    (tenant_id, sku, quantity, revision)
VALUES (7, 'cable', 4, 1)
ON CONFLICT (tenant_id, sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    revision = EXCLUDED.revision
WHERE EXCLUDED.revision > current_stock.revision
RETURNING tenant_id, sku, quantity, revision;

Aucune ligne n’a été renvoyée. L’enregistrement est resté à la quantité 15, révision 2. La documentation de RETURNING explique comment récupérer les données des lignes modifiées ; cette clause n’est pas une recherche générale parmi les lignes que l’instruction a choisi de ne pas modifier.

Appliquez maintenant la même règle de conflit à une clé absente :

INSERT INTO upsert_lab.stock AS current_stock
    (tenant_id, sku, quantity, revision)
VALUES (7, 'adapter', 6, 1)
ON CONFLICT (tenant_id, sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    revision = EXCLUDED.revision
WHERE EXCLUDED.revision > current_stock.revision
RETURNING tenant_id, sku, quantity, revision;

Sortie observée :

7|adapter|6|1

L’adaptateur a été inséré avec une quantité de 6 et une révision de 1. Sans conflit, il n’existait aucune révision à comparer. La condition WHERE de la mise à jour ne filtre pas les insertions. Utilisez des contraintes ou validez les données entrantes lorsqu’une règle doit aussi s’appliquer aux nouvelles lignes.

Traiter les clés répétées avant d’envoyer un lot

L’instruction suivante est volontairement invalide pour nos données. Exécutez-la séparément avec l’autocommit activé :

INSERT INTO upsert_lab.stock AS current_stock
    (tenant_id, sku, quantity, revision)
VALUES (7, 'cable', 20, 3), (7, 'cable', 25, 4)
ON CONFLICT (tenant_id, sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    revision = EXCLUDED.revision
WHERE EXCLUDED.revision > current_stock.revision;

Elle a échoué avec le code SQLSTATE 21000, une violation de cardinalité, car elle tentait de modifier deux fois le même câble dans une seule instruction. Un contrôle ultérieur a retrouvé la quantité 15 et la révision 2 : la première tentative de mise à jour de l’instruction échouée n’a pas été conservée.

Avant l’écriture, réduisez chaque lot à une ligne acceptée par clé de conflit. Pour ces enregistrements versionnés, une règle possible consiste à conserver la révision la plus élevée, à condition que des révisions égales portent les mêmes données. Rejetez ou isolez les égalités contradictoires. L’ordre d’arrivée ne remplace pas une règle définie, et l’instruction ci-dessus ne choisit pas silencieusement sa dernière ligne comme gagnante.

Si une instruction échoue dans une transaction explicite, effectuez un rollback ou revenez à un point de sauvegarde approprié avant de continuer. Ce guide utilise des instructions séparées en autocommit pour que l’erreur intentionnelle n’empêche pas les exemples suivants de fonctionner.

Distinguer le remplacement d’une valeur de l’ajout d’un delta

Un compteur n’a pas le même sens qu’un état de stock. Créez une table distincte pour cet exemple :

CREATE TABLE upsert_lab.counters (
    counter_key text PRIMARY KEY,
    total bigint NOT NULL CHECK (total >= 0)
);

Exécutez cette instruction deux fois :

INSERT INTO upsert_lab.counters AS c (counter_key, total)
VALUES ('imports', 3)
ON CONFLICT (counter_key) DO UPDATE
SET total = c.total + EXCLUDED.total
RETURNING counter_key, total;

La première exécution a renvoyé imports|3, la seconde imports|6. Ici, c.total représente le total stocké, et EXCLUDED.total l’incrément reçu.

Ce résultat est correct pour deux incréments distincts. Il est incorrect si la seconde exécution rejoue le même événement. ON CONFLICT n’attribue pas d’identité à l’événement et n’empêche pas d’appliquer un delta deux fois. Si les nouvelles tentatives ne doivent pas compter en double, prévoyez la déduplication de l’événement et la mise à jour du compteur dans une même transaction. Ce guide n’implémente pas ce traitement applicatif.

Conserver la visibilité sur les autres erreurs de contrainte

Nos tests d’échec ont établi plusieurs limites :

• Une quantité négative a toujours déclenché une erreur CHECK avec DO NOTHING.
• Un identifiant de tenant nul a toujours échoué sur NOT NULL.
• Cibler une clé unique n’a pas masqué la violation d’une autre clé unique dans une table de test distincte.
• Une cible de conflit sans clé unique correspondante a échoué avant qu’un upsert utile puisse avoir lieu.

Nous avons aussi testé les clés uniques acceptant NULL dans des tables temporaires. Une contrainte UNIQUE ordinaire a accepté deux valeurs nulles ; UNIQUE NULLS NOT DISTINCT les a traitées comme un conflit. La table de stock évite cette ambiguïté grâce à sa clé primaire non nulle. Définissez le sens d’un identifiant métier manquant avant de choisir sa règle d’unicité.

Tenir compte des transactions, puis nettoyer le laboratoire

PostgreSQL documente une décision atomique entre insertion et mise à jour pour ON CONFLICT DO UPDATE, sous réserve d’erreurs indépendantes. Notre exemple conditionnel peut aussi ignorer une mise à jour lorsque le test de révision est faux. L’atomicité ne garantit ni l’absence d’attente face à une transaction concurrente, ni la protection de tout un traitement métier par cette seule instruction. Consultez la documentation de l’isolation des transactions pour les règles de concurrence.

Nos contrôles étaient séquentiels ; ils ne constituent pas un test d’écritures concurrentes ou de performance. Avant le déploiement, testez les droits de l’application, la contention, les nouvelles tentatives et les rollbacks avec votre charge réelle. Si vous examinez une écriture avec EXPLAIN ANALYZE, cette commande exécute l’instruction. Notre guide PostgreSQL EXPLAIN, en anglais explique cette distinction.

Supprimez uniquement les tables jetables créées pour cet exercice :

DROP TABLE upsert_lab.counters;
DROP TABLE upsert_lab.stock;
DROP SCHEMA upsert_lab;

Le programme de test a vérifié la disparition du schéma, arrêté le serveur PostgreSQL local et supprimé son fichier de mot de passe temporaire. Aucun comportement lié au cloud, aux triggers, aux index partiels, aux partitions ou aux rôles de production n’a été testé.