PostgreSQL INSERT ON CONFLICT : Upsert Correct avec Cibles Uniques, DO NOTHING vs DO UPDATE

Identifier la règle d'unicité réelle

Une clause ON CONFLICT résout les violations des contraintes ou des index d'arbitre. Il s'agit de contraintes d'unicité (PRIMARY KEY, UNIQUE) ou d'index d'unicité. La documentation indique qu'un conflict_target doit être fourni pour ON CONFLICT DO UPDATE. Cette cible spécifie quels conflits déclenchent l'action alternative en choisissant des index d'arbitre. La règle est appliquée au niveau de la base de données, et non par la logique de l'application. Par exemple, une table inventory(sku text PRIMARY KEY, qty integer NOT NULL) a une contrainte de clé primaire sur sku. Cette contrainte est l'arbitre ; toute insertion d'une valeur sku dupliquée est un conflit.

Choisir la cible du conflit

La cible du conflit peut utiliser l'inférence d'index unique, le nommage de colonnes/expressions, ou nommer directement une contrainte avec ON CONFLICT ON CONSTRAINT. L'inférence est souvent préférable. Pour la table inventory, la clé primaire sur sku est inférée par ON CONFLICT (sku). La documentation note que l'inférence choisit tous les index uniques qui contiennent exactement les colonnes/expressions spécifiées. Si vous nommez une contrainte directement, elle utilise l'index associé à cette contrainte. Pour les index uniques partiels, vous devez inclure une clause WHERE dans la cible du conflit pour correspondre au prédicat de l'index.

Utiliser DO NOTHING délibérément

ON CONFLICT DO NOTHING rejette silencieusement les lignes qui causeraient un conflit avec n'importe quelle contrainte ou index d'arbitre. La cible du conflit est facultative ; si elle est omise, les conflits avec toutes les contraintes d'unicité utilisables sont gérés. Utilisez ceci lorsque vous souhaitez insérer uniquement de nouvelles lignes et ignorer les doublons. Par exemple, INSERT INTO inventory (sku, qty) VALUES ('A', 3) ON CONFLICT DO NOTHING ; ne fait rien si SKU 'A' existe déjà. La déclaration réussit, et le compte renvoyé indique le nombre de lignes réellement insérées (zéro dans ce cas).

Mettre à jour à partir des valeurs exclues

ON CONFLICT DO UPDATE modifie la ligne existante en conflit. Dans la clause SET, l'alias spécial EXCLUDED fournit les valeurs proposées à l'origine pour l'insertion. La documentation précise que lors de la référence d'une colonne, n'incluez pas le nom de la table. Pour l'exemple inventory, INSERT INTO inventory (sku, qty) VALUES ('A', 3) ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty ; remplace la quantité existante par 3. Il n'ajoute pas les valeurs. Pour effectuer une mise à jour additive, vous devez explicitement référencer la ligne existante : SET qty = inventory.qty + EXCLUDED.qty.

Tracer un exemple à deux lignes

Considérez l'insertion de deux lignes où l'une est en conflit et l'autre ne l'est pas. Avec inventory contenant initialement ('A', 2), exécutez : INSERT INTO inventory (sku, qty) VALUES ('A', 3), ('B', 5) ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty ;. La ligne pour SKU 'A' est en conflit, déclenchant une mise à jour qui définit sa quantité sur 3. La ligne pour SKU 'B' n'est pas en conflit et est insérée. La commande renvoie INSERT 0 2, indiquant que deux lignes ont été traitées (une mise à jour, une insertion). L'état final est ('A', 3), ('B', 5).

Gérer les lignes en double dans une seule déclaration

PostgreSQL documente ON CONFLICT DO UPDATE comme déterministe : une seule commande ne peut pas affecter la même ligne existante plus d'une fois. Avec des clés proposées répétées, telles que VALUES ('A', 3), ('A', 4), une violation de cardinalité peut se produire. Il ne s'agit pas d'une erreur de clé dupliquée ordinaire se produisant avant que ON CONFLICT n'ait la possibilité d'agir. Ne vous fiez pas à la valeur proposée première ou dernière gagnant.

Dédoublez les entrées ou agrégez les clés répétées selon une règle métier explicite avant de soumettre la déclaration. Pour les quantités de remplacement, décidez quelle observation doit l'emporter ; pour les quantités additives, décidez si l'addition est appropriée. En cas d'erreur de déclaration, ses modifications ne deviennent pas une mise à jour réussie partielle. Dans une transaction explicite, récupérez à l'aide de la politique de rollback ou de point de sauvegarde de l'application.

Comprendre le comportement concurrent

ON CONFLICT DO UPDATE garantit un résultat atomique INSERT ou UPDATE pour chaque ligne, même en cas de forte concurrence. Cependant, la documentation avertit que lors de l'exécution de CREATE INDEX CONCURRENTLY ou REINDEX CONCURRENTLY sur un index unique, INSERT ... ON CONFLICT sur la même table peut échouer de manière inattendue avec une violation d'unicité. La déclaration prend un verrou sur la ligne en conflit. Si deux transactions concurrentes tentent d'insérer la même clé, l'une réussira avec une insertion, et l'autre entrera en conflit et prendra l'action DO UPDATE sur la ligne désormais existante.

Vérifier les limites au-delà de la déclaration

La mise à jour atomique ne garantit pas un traitement exactement-une fois au niveau de l'entreprise. La répétition d'une mise à jour additive peut incrémenter une quantité à nouveau, à moins que l'application ne déduplique l'opération logique. D'autres contraintes, autorisations et déclencheurs peuvent encore rejeter la déclaration. Par défaut, les colonnes d'unicité nullables traitent les valeurs nulles comme distinctes, permettant plusieurs valeurs nulles, sauf si NULLS NOT DISTINCT est spécifié. Les index uniques partiels s'appliquent uniquement à leur prédicat ; l'inférence de cible de conflit doit sélectionner un arbitre approprié.