如何處理 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 追加行

與前面的方法類似，但使用SELECT 來追加行而不是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 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 表達式

此方法使用一行 NULL 值作為 VALUES 表達式的第一行，有效地定義列類型。後續行無需明確轉換即可更新。

...
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中文網其他相關文章！