Comment gérer les valeurs NULL lors de la mise à jour de plusieurs lignes dans PostgreSQL ?

Casting NULL lors de la mise à jour de plusieurs lignes

Lors de la mise à jour de plusieurs lignes dans une table à l'aide d'une seule requête, il est essentiel de s'assurer que les valeurs étant attribués correspondent aux types de données des colonnes impliquées. Si des valeurs NULL sont impliquées, cela peut entraîner des erreurs dues à des incompatibilités de types.

Présentation du problème

Considérez la requête suivante :

UPDATE foo SET x=t.x, y=t.y
FROM (
  VALUES (50, 50, 1),
         (100, 120, 2)
)
AS t(x, y, pkid)
WHERE foo.pkid=t.pkid

Cette requête fonctionne pour les valeurs non NULL, mais lorsque des valeurs NULL sont introduites, une erreur se produit :

UPDATE foo SET x=t.x, y=t.y
FROM (
  VALUES (null, 20, 1),
         (null, 50, 2)
)
AS t(x, y, pkid)
WHERE foo.pkid=t.pkid

L'erreur est causée par la spécification de type manquante pour les valeurs NULL. PostgreSQL tente de deviner leur type en fonction du littéral, ce qui entraîne une incompatibilité avec la colonne entière x.

Solutions

Pour résoudre ce problème, plusieurs solutions peuvent être utilisées :

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

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;

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

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;

2. VALEURS Expression avec type par colonne

UPDATE foo f
SET    x = t.x
     , y = t.y
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.
WHERE  f.pkid = t.pkid;

3. VALEURS Expression avec type de ligne

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;

4. VALEURS Expression avec type de ligne décomposé

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

5. VALEURS Expression avec des types extraits du type de ligne

UPDATE foo f
SET   (  x,   y)
    = (t.x, t.y)  -- short notation, see below
FROM (
   VALUES
      ((NULL::foo).pkid, (NULL::foo).x, (NULL::foo).y)  -- subset of columns
    , (1, 20, NULL)
    , (2, 50, NULL)
   ) t(pkid, x, y)       -- arbitrary column names (I made them match)
WHERE  f.pkid = t.pkid;

Le choix de la solution dépend de facteurs tels que les performances, la commodité et le nombre de colonnes impliquées. Les solutions 4 et 5 sont généralement recommandées pour leur simplicité et leur flexibilité.

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!