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

複数の行を更新するときに NULL をキャストする

単一のクエリを使用してテーブル内の複数の行を更新する場合、値が割り当てられたものは、関係する列のデータ型と一致します。 NULL 値が含まれる場合、型の不一致によりエラーが発生する可能性があります。

問題の概要

次のクエリを検討してください:

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

このクエリは NULL 以外の値に対して機能しますが、NULL 値が導入されるとエラーが発生します。発生:

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

このエラーは、NULL 値の型指定が欠落していることが原因で発生します。 PostgreSQL はリテラルに基づいて型を推測しようとするため、整数列 x との不一致が生じます。

解決策

この問題を解決するには、いくつかの解決策を使用できます。 :

0。 LIMIT 0 で行を選択し、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 で行を追加します。 LIMIT 0 で行を選択し、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 で行を追加します。列ごとのタイプの VALUES 式

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.行タイプの 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;

4.分解された行タイプの 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

5.行タイプからフェッチされた型を使用した VALUES 式

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;

ソリューションの選択は、パフォーマンス、利便性、関係する列の数などの要因によって異なります。一般的には、単純さと柔軟性を考慮して、解決策 4 と 5 をお勧めします。

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