Comment mettre à jour efficacement plusieurs lignes avec des valeurs NULL dans PostgreSQL ?

Défis liés à la mise à jour de plusieurs lignes avec des valeurs NULL

Dans PostgreSQL, la mise à jour simultanée de plusieurs lignes peut rencontrer des défis lorsqu'il s'agit de valeurs NULL. Lorsque vous travaillez avec des expressions VALUES autonomes, PostgreSQL manque d'informations sur les types de données. Par conséquent, une conversion explicite est requise pour les littéraux non numériques, y compris les valeurs NULL.

Casting de valeurs NULL dans les requêtes de mise à jour

Pour résoudre ce problème, explorez les solutions suivantes :

1. Sous-requête avec LIMIT 0

UPDATE foo SET x=t.x, y=t.y FROM
(
  (SELECT pkid, x, y FROM foo LIMIT 0)
   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;

• Avantages : surcharge minimale, simple et rapide, nécessite la connaissance des noms de colonnes pertinents
• Inconvénient : la résolution de type peut échouer pour certains types de données

2. Sous-requête avec LIMIT 0 et SELECT

UPDATE foo SET x=t.x, y=t.y FROM
(
  (SELECT pkid, x, y FROM foo LIMIT 0)
   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;

• Avantages : évite les échecs de résolution de type
• Inconvénients : syntaxe verbeuse par ligne, plus lente que l'expression VALUES pour les listes longues

3. VALEURS Expression avec type par colonne

...
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.
...

• Avantages : généralement plus rapide que #2, syntaxe courte pour les colonnes pertinentes, connaissance requise uniquement des noms de colonnes pertinents
• Inconvénients : syntaxe verbeuse pour quelques lignes, moins lisible

4. 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;

• Avantages : syntaxe la plus rapide et la plus courte pour quelques lignes et toutes les colonnes, pas besoin de spécifier les noms de colonnes
• Inconvénients : syntaxe peu connue, nécessite la connaissance du nombre et de la position des colonnes pertinentes dans l'ordre par défaut

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

...
FROM (
   VALUES
      ((NULL::foo).*)  -- decomposed row of values
    , (1, 20, NULL)    -- uniform syntax for all
    , (2, 50, NULL)
   ) t(pkid, x, y)  -- arbitrary column names (I made them match)
...

• Avantages et inconvénients similaires au n°4, mais avec une syntaxe plus connue

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!