PostgreSQL の複数行更新で NULL 値を処理する方法は?

複数行更新での NULL 値のキャスト

PostgreSQL で複数の行を NULL 値で更新すると、スタンドアロン VALUES 式の型データが存在しないため、エラーが発生する可能性があります。この問題を解決するための解決策をいくつか紹介します。

1. LIMIT 0 で行を選択し、UNION ALL VALUES で行を追加

このメソッドは、LIMIT 0 サブクエリを使用してテーブルから列名と型を取得します。これは行タイプを定義し、更新された値をキャストするために使用されます。

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. LIMIT 0 で行を選択し、UNION ALL SELECT で行を追加

前の方法と似ていますが、VALUES 式の代わりに 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;

3.列ごとの型を使用した VALUES 式

このアプローチでは、VALUES 式の最初の行として NULL 値の行を使用し、列の型を効果的に定義します。後続の行は、明示的なキャストを行わずに更新できます。

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

このメソッドは、行タイプを使用して列タイプを暗黙的に定義します。行はテーブルを表す行タイプにキャストされ、フィールド選択を使用して個々の列にアクセスできます。

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 式

前の方法と似ていますが、標準構文で分解された行の値を使用します。

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

結論

LIMIT 0 で行を選択すると、高速で広く使用されている方法ですが、一部の値の型解決が不可能な場合は失敗する可能性があります。他の方法は、関係する列と行の数に基づいてパフォーマンスへの影響が異なる代替アプローチを提供します。最終的に、どの方法を選択するかは、特定の要件と既存のコードとの互換性によって決まります。

以上がPostgreSQL の複数行更新で NULL 値を処理する方法は?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。