在 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.具有行型別

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 表達式。具有分解行類型的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中文網其他相關文章！