如何在 PostgreSQL 中高效更新多行 NULL 值？

更新具有 NULL 值的多行的挑战

在 PostgreSQL 中，同时更新多行在处理 NULL 值时可能会遇到挑战。当使用独立的 VALUES 表达式时，PostgreSQL 缺乏有关数据类型的信息。因此，非数字文字（包括 NULL 值）需要显式转换。

在更新查询中转换 NULL 值

要解决此问题，请探索以下解决方案：

1。带有 LIMIT 0 的子查询

UPDATE foo SET x=t.x, y=t.y FROM
(
  (SELECT pkid, x, y FROM foo LIMIT 0)
   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 和 SELECT 的子查询

UPDATE foo SET x=t.x, y=t.y FROM
(
  (SELECT pkid, x, y FROM foo LIMIT 0)
   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;

• 优点：避免类型解析失败
• 缺点：每行语法冗长，比长列表的 VALUES 表达式慢

3.每列类型的 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.
...

• 优点：通常比 #2 更快，相关列的语法较短，仅需要相关列名称的知识
• 缺点：冗长的语法对于几行，可读性较差

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 表达式

...
FROM (
   VALUES
      ((NULL::foo).*)  -- decomposed row of values
    , (1, 20, NULL)    -- uniform syntax for all
    , (2, 50, NULL)
   ) t(pkid, x, y)  -- arbitrary column names (I made them match)
...

• 优点和缺点与 #4 类似，但具有更常见的语法

以上是如何在 PostgreSQL 中高效更新多行 NULL 值？的详细内容。更多信息请关注PHP中文网其他相关文章！