更新多行时强制转换 NULL 类型 [英] Casting NULL type when updating multiple rows

问题描述

当我尝试同时更新多行时遇到问题.

I have a problem when I try to update many rows at the same time.

这是我使用的表和查询(为了更好的阅读而进行了简化):

Here is the table and query I use (simplified for better reading):

表格

CREATE TABLE foo
(
    pkid integer,
    x integer,
    y integer
)

查询

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

此查询完美无缺，但是当我尝试执行所有 x 或 y 值都为空的查询时，出现错误:

This query works perfectly, but when I try to execute a query where all x or y values are null, I get an error:

带有空值的查询

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

错误

ERROR:  column "x" is of type integer but expression is of type text
LINE 1: UPDATE foo SET x=t.x FROM

解决这个问题的唯一方法是将至少一个值 (null, 20, 1) 更改为 (null:int, 50, 2) 但是我不能这样做，因为我有一个函数可以生成这些更新多行"查询，但它对列类型一无所知.

The only way to fix that is to change at least one of the values (null, 20, 1) to (null:int, 50, 2) but I can't do that, since I have a function which generates these "update multiple rows" query and it doesn't know anything about the column types.

这里最好的解决方案是什么?是否有更好的多行更新查询?有没有类似 AS t(x:gettype(foo.x), y:gettype(foo.y), pkid:gettype(foo.pkid)) 的函数或语法?

What's the best solution here? Is there any better update query for multiple rows? Is there any function or syntax like AS t(x:gettype(foo.x), y:gettype(foo.y), pkid:gettype(foo.pkid))?

推荐答案

使用独立的 VALUES 表达式，PostgreSQL 不知道数据类型应该是什么.对于简单的数字文字，系统很乐意假设匹配类型.但是对于其他输入(如 NULL)，您需要显式转换 - 正如您已经发现的那样.

With a standalone VALUES expression PostgreSQL has no idea what the data types should be. With simple numeric literals the system is happy to assume matching types. But with other input (like NULL) you would need to cast explicitly - as you already have found out.

您可以查询 pg_catalog(快速，但特定于 PostgreSQL)或 information_schema(缓慢，但标准 SQL)以找出并准备具有适当类型的语句.

You can query pg_catalog (fast, but PostgreSQL-specific) or the information_schema (slow, but standard SQL) to find out and prepare your statement with appropriate types.

或者您可以使用这些简单的技巧"之一(我把最好的留到了最后):

Or you can use one of these simple "tricks" (I saved the best for last):

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;

子查询的第一个子选择:

The first sub-select of the subquery:

(SELECT x, y, pkid  FROM foo LIMIT 0)

获取列的名称和类型，但 LIMIT 0 阻止它添加实际行.随后的行被强制为现在明确定义的行类型 - 并立即检查它们是否与类型匹配.应该是对原始形式的细微改进.

gets names and types for the columns, but LIMIT 0 prevents it from adding an actual row. Subsequent rows are coerced to the now well-defined row type - and checked immediately whether they match the type. Should be a subtle additional improvement over your original form.

在为表的所有列提供值时，这种简短的语法可用于第一行:

While providing values for all columns of the table this short syntax can be used for the first row:

(TABLE foo LIMIT 0)

主要限制:Postgres 将独立 VALUES 表达式的输入文字转换为尽力而为"的表达式.立即键入.当它稍后尝试转换为第一个 SELECT 的给定类型时，如果在假定类型和目标类型之间没有注册的赋值转换，对于某些类型来说可能已经太晚了.示例:text ->timestamp 或 text ->json.

Major limitation: Postgres casts the input literals of the free-standing VALUES expression to a "best-effort" type immediately. When it later tries to cast to the given types of the first SELECT, it may already be too late for some types if there is no registered assignment cast between the assumed type and the target type. Examples: text -> timestamp or text -> json.

优点:

• 最低开销.
• 可读、简单且快速.
• 您只需要知道表的相关列名即可.

缺点:

• 某些类型的类型解析可能会失败.

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;

优点:

• 像0.，但避免了类型解析失败.

缺点:

对于长行列表，
• UNION ALL SELECT 比 VALUES 表达式慢，正如您在测试中发现的那样.
• 每行的详细语法.

• UNION ALL SELECT is slower than VALUES expression for long lists of rows, as you found in your test.
• Verbose syntax per row.

...
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.
...

与0相反.这避免了过早的类型解析.

Contrary to 0. this avoids premature type resolution.

VALUES 表达式中的第一行是一行 NULL 值，它定义了所有后续行的类型.这个领先的噪声行稍后由 WHERE f.pkid = t.pkid 过滤，因此它永远不会出现.出于其他目的，您可以在子查询中使用 OFFSET 1 消除添加的第一行.

The first row in the VALUES expression is a row of NULL values which defines the type for all subsequent rows. This leading noise row is filtered by WHERE f.pkid = t.pkid later, so it never sees the light of day. For other purposes you can eliminate the added first row with OFFSET 1 in a subquery.

优点:

• 通常比1.(甚至0.)快
• 具有多列且只有少数相关的表的简短语法.
• 您只需要知道表的相关列名即可.

缺点:

• 只有几行的详细语法
• 可读性较差 (IMO).

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;

你显然知道表名.如果您还知道列数及其顺序，则可以使用此方法.

You obviously know the table name. If you also know the number of columns and their order you can work with this.

对于 PostgreSQL 中的每个表，都会自动注册一个行类型.如果匹配表达式中的列数，则可以转换为表的行类型 ('(1,50,)'::foo) 从而隐式分配列类型.在逗号后面不加任何内容以输入 NULL 值.为每个不相关的尾随列添加一个逗号.
在下一步中，您可以使用演示的语法访问各个列.在手册中详细了解字段选择.

For every table in PostgreSQL a row type is registered automatically. If you match the number of columns in your expression, you can cast to the row type of the table ('(1,50,)'::foo) thereby assigning column types implicitly. Put nothing behind a comma to enter a NULL value. Add a comma for every irrelevant trailing column.
In the next step you can access individual columns with the demonstrated syntax. More about Field Selection in the manual.

或者您可以添加一行 NULL 值并对实际数据使用统一的语法:

Or you could add a row of NULL values and use uniform syntax for actual data:

...
  VALUES
      ((NULL::foo))  -- row of NULL values
    , ('(1,20,)')    -- uniform ROW value syntax for all
    , ('(2,50,)')
...

优点:

• 最快(至少在我的行和列很少的测试中).
• 需要所有列的少数行或表格的最短语法.
• 您不必拼写表格的列 - 所有列都会自动具有匹配的名称.

缺点:

• 从记录/行/复合类型中选择字段的语法不是很广为人知.
• 您需要按默认顺序了解相关列的数量和位置.

像3.，但使用标准语法分解行:

Like 3., but with decomposed rows in standard syntax:

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

或者，再次使用空值的前导行:

Or, with a leading row of NULL values again:

...
   VALUES
      ((NULL::foo).*)  -- row of NULL values
    , (1, 20, NULL)    -- uniform syntax for all
    , (2, 50, NULL)
...

优缺点与3.类似，但语法更广为人知.
并且您需要拼出列名称(如果需要).

Pros and cons like 3., but with more commonly known syntax.
And you need to spell out column names (if you need them).

像 Unril 评论的，我们可以结合2. 和 4. 的优点仅提供列的子集:

Like Unril commented, we can combine the virtues of 2. and 4. to provide only a subset of columns:

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.类似，但我们可以处理任何列子集，而不必知道完整列表.

Pros and cons like 4., but we can work with any subset of columns and don't have to know the full list.

还显示 UPDATE 本身的简短语法，这对于具有多列的情况很方便.相关:

Also displaying short syntax for the UPDATE itself that's convenient for cases with many columns. Related:

• 批量更新所有列

4.和 5. 是我的最爱.

db<>fiddle 这里全部展示

这篇关于更新多行时强制转换 NULL 类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！