如何处理 PostgreSQL 多行更新中的 NULL 类型转换？

在多行更新中强制转换 NULL 类型

在 PostgreSQL 中，在多行上执行更新查询可能会导致处理 NULL 时出错如果未显式转换列类型的值。本文探讨了此问题的几种解决方案，提供了替代方法来确保多行更新期间正确的类型转换。

解决方案 1：使用 VALUES 和 UNION ALL 选择 Limit 0

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM  (
  (SELECT pkid, x, y FROM foo LIMIT 0) -- Get column types
   UNION ALL
   VALUES
      (1, 20, NULL)  -- No type casts
    , (2, 50, NULL)
   ) t               -- Column names and types defined
WHERE  f.pkid = t.pkid;

此方法结合了 LIMIT 为 0 的 SELECT 语句来检索列名称和类型，然后使用UNION ALL 运算符。子查询的第一行确保为后续行定义适当的列类型。

解决方案 2：使用 VALUES 和 UNION ALL SELECT 选择 Limit 0

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM  (
  (SELECT pkid, x, y FROM foo LIMIT 0) -- Get column types
   UNION ALL SELECT 1, 20, NULL
   UNION ALL SELECT 2, 50, NULL
   ) t               -- Column names and types defined
WHERE  f.pkid = t.pkid;

与解决方案 1 类似，此方法使用 SELECT 获取列类型，然后使用单独的 SELECT 语句附加数据行，防止任何过早类型

解决方案 3：具有每列类型的 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.
...

此解决方案定义 VALUES 表达式本身中的列类型，确保后续行由于自动类型假设，被转换为这些类型而不会遇到任何错误。

解决方案4：具有行类型的 VALUES 表达式

UPDATE foo f
SET x = (t.r).x         -- Parenthesis for unambiguous syntax
  , y = (t.r).y
FROM (
   VALUES
      ('(1,20,)'::foo)  -- Columns need to be in table default order
     ,('(2,50,)')       -- Nothing after 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 (match table columns)
WHERE  f.pkid = t.pkid;     -- Eliminates 1st row with NULL values

与解决方案 4 类似，但使用分解行指定数据值。这允许您仅提供相关列，而无需了解表中所有列的完整顺序和类型。

选择最佳解决方案取决于性能、便利性和可用性等因素有关列类型的信息。

以上是如何处理 PostgreSQL 多行更新中的 NULL 类型转换？的详细内容。更多信息请关注PHP中文网其他相关文章！