如何处理 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中文网其他相关文章！