UPSERT (ON CONFLICT)
Un UPSERT insère une ligne ou, si sa clé existe déjà, met à jour la ligne en place. C'est la façon sûre de synchroniser un catalogue ou un import sans tester l'existence au préalable.
- upsert
- ON CONFLICT
- DO UPDATE
- DO NOTHING
- excluded
- MERGE
Syntaxe
INSERT INTO table (cle, col)VALUES (v1, v2)ON CONFLICT (cle) DO UPDATE SET col = excluded.col;-- ou : ON CONFLICT (cle) DO NOTHINGExemple
INSERT INTO products (id, name, price, category)VALUES (2, 'Souris sans fil', 24.99, 'Informatique'), (6, 'Tapis de souris', 12.50, 'Informatique')ON CONFLICT (id) DO UPDATE SET price = excluded.price;SELECT id, name, priceFROM productsWHERE id IN (2, 6)ORDER BY id;-- Jeu de données : boutique-- Le produit 2 existe : son prix est mis à jour. Le produit 6 est créé.-- Résultat attendu :-- 2 | Souris sans fil | 24.99-- 6 | Tapis de souris | 12.5À retenir
- excluded désigne la ligne qu'on tentait d'insérer : SET price = excluded.price reprend la nouvelle valeur, SET stock = stock + excluded.stock cumule.
- La cible du conflit (id) doit correspondre à une clé primaire ou à une contrainte UNIQUE, sinon la requête est refusée.
- Piège : INSERT OR REPLACE n'est pas un UPSERT. Il supprime puis réinsère la ligne : les colonnes non fournies repassent à leur valeur par défaut et les actions ON DELETE se déclenchent.
- Même syntaxe ON CONFLICT dans PostgreSQL (9.5+). MySQL utilise ON DUPLICATE KEY UPDATE, SQL Server et Oracle l'instruction MERGE.
- Dans SQLite, INSERT … SELECT … ON CONFLICT demande un WHERE dans le SELECT (WHERE true suffit) pour lever une ambiguïté d'analyse.
Voir aussi
- INSERTINSERT ajoute de nouvelles lignes dans une table, une par une, par lot ou à partir d'une requête. Les colonnes non citées prennent leur valeur par défaut, ou NULL si aucune n'est prévue.
- UPDATEUPDATE modifie les valeurs de lignes existantes. La clause WHERE désigne les lignes visées, SET les nouvelles valeurs, qui peuvent se calculer à partir des anciennes (hausse de prix, statut).
- UNIQUE, NOT NULL, CHECKCes contraintes refusent les données invalides dès l'écriture : NOT NULL impose une valeur, UNIQUE interdit les doublons, CHECK valide une règle métier comme une remise comprise entre 1 et 50 %.
- RETURNINGRETURNING renvoie les lignes touchées par un INSERT, un UPDATE ou un DELETE, dans la même instruction. On récupère ainsi l'identifiant généré ou les nouvelles valeurs sans requête supplémentaire.