Comment gérer les valeurs NULL dans les mises à jour multi-lignes PostgreSQL ?

Casting de valeurs NULL dans les mises à jour multi-lignes

La mise à jour de plusieurs lignes avec des valeurs NULL dans PostgreSQL peut entraîner des erreurs en raison de l'absence de données de type pour les expressions VALUES autonomes. Voici quelques solutions pour surmonter ce problème :

1. Sélectionnez la ligne avec LIMIT 0, ajoutez les lignes avec UNION ALL VALUES

Cette méthode utilise une sous-requête LIMIT 0 pour récupérer les noms et types de colonnes de la table. Ceci définit le type de ligne, qui est ensuite utilisé pour convertir les valeurs mises à jour.

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM  (
  (SELECT pkid, x, y FROM foo LIMIT 0) -- parenthesis needed with LIMIT
   UNION ALL
   VALUES
      (1, 20, NULL)  -- no type casts here
    , (2, 50, NULL)
   ) t               -- column names and types are already defined
WHERE  f.pkid = t.pkid;

2. Sélectionnez une ligne avec LIMIT 0, ajoutez des lignes avec UNION ALL SELECT

Similaire à la méthode précédente, mais utilise un SELECT pour ajouter des lignes au lieu d'une expression VALUES, évitant ainsi les problèmes potentiels de résolution de type.

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM  (
  (SELECT pkid, x, y FROM foo LIMIT 0) -- parenthesis needed with LIMIT
   UNION ALL SELECT 1, 20, NULL
   UNION ALL SELECT 2, 50, NULL
   ) t               -- column names and types are already defined
WHERE  f.pkid = t.pkid;

3. Expression VALUES avec type par colonne

Cette approche utilise une ligne de valeurs NULL comme première ligne d'une expression VALUES, définissant efficacement les types de colonnes. Les lignes suivantes peuvent être mises à jour sans diffusion explicite.

...
FROM  (
   VALUES 
     ((SELECT pkid FROM foo LIMIT 0)
    , (SELECT x    FROM foo LIMIT 0)
    , (SELECT y    FROM foo LIMIT 0))  -- get type for each col individually
   , (1, 20, NULL)
   , (2, 50, NULL)
   ) t (pkid, x, y)  -- columns names not defined yet, only types.
...

4. VALUES Expression avec type de ligne

Cette méthode utilise des types de lignes pour définir implicitement les types de colonnes. La ligne est convertie en un type de ligne représentant le tableau et les colonnes individuelles sont accessibles à l'aide de la sélection de champs.

UPDATE foo f
SET x = (t.r).x         -- parenthesis needed to make syntax unambiguous
  , y = (t.r).y
FROM (
   VALUES
      ('(1,20,)'::foo)  -- columns need to be in default order of table
     ,('(2,50,)')       -- nothing after the last comma for NULL
   ) t (r)              -- column name for row type
WHERE  f.pkid = (t.r).pkid;

5. VALUES Expression avec type de ligne décomposé

Similaire à la méthode précédente, mais utilise des valeurs de ligne décomposées dans la syntaxe standard.

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM (
   VALUES
      (('(1,20,)'::foo).*)  -- decomposed row of values
    , (2, 50, NULL)
   ) t(pkid, x, y)  -- arbitrary column names (I made them match)
WHERE  f.pkid = t.pkid;     -- eliminates 1st row with NULL values

Conclusion

Alors que la sélection d'une ligne avec LIMIT 0 est Il s'agit d'une méthode rapide et largement utilisée, mais elle peut échouer si la résolution de type n'est pas possible pour certaines valeurs. Les autres méthodes proposent des approches alternatives dont les implications en termes de performances varient en fonction du nombre de colonnes et de lignes impliquées. En fin de compte, le choix de la méthode dépend des exigences spécifiques et de la compatibilité avec le code existant.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!