更新多行时强制转换空类型

Question

当我试图同时更新多行时出现问题。

以下是我使用的表和查询（为了更好地阅读，将其简化）：

表

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值都是null的查询时，我得到一个错误：

带空值的查询

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），但我不能这样做，因为我有一个生成这些更新多行查询的函数，它对列类型一无所知。

这里最好的解决方案是什么？有没有更好的多行更新查询？有没有像AS t（x： gettype（foo. x），y： gettype（foo. y），pchild： gettype（foo.pkid））这样的函数或语法？

Answer 1

脚本将从foo创建一个临时表。它将具有与foo相同的数据类型。使用不可能的条件，使其为空：

select x, y, pkid
into temp t
from foo
where pkid = -1

将脚本插入其中：

insert into t (x, y, pkid) values
(null, 20, 1),
(null, 50, 2)

现在更新一下：

update foo 
set x=t.x, y=t.y 
from t
where foo.pkid=t.pkid

最后放下它：

drop table t

Answer 2

如果有一个生成查询的脚本，可以提取并缓存每列的数据类型，并相应地创建类型转换。例如：

SELECT column_name,data_type,udt_name 
FROM information_schema.columns 
WHERE table_name = 'foo';

从这个udt_的名字中，你将得到你在最后一段中解释的必要演员阵容。此外，您还可以这样做：

UPDATE foo
SET x = t.x
FROM (VALUES(null::int4,756),(null::int4,6300))
AS t(x,pkid)
WHERE foo.pkid = t.pkid;

Answer 3

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

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

或者你可以使用这些简单的“技巧”（我把最好的留到最后）：

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;

子查询的第一个子选择：

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

获取列的名称和类型，但是LIMIT 0阻止它添加实际的行。后续行被强制为现在定义良好的行类型，并立即检查它们是否与类型匹配。这应该是对原始形式的一个微妙的额外改进。

在为表的所有列提供值的同时，第一行可以使用这种简短的语法：

(TABLE foo LIMIT 0)

主要限制：Postgres将独立的值表达式的输入文本立即转换为“尽力”类型。当它稍后尝试强制转换到第一个选择的给定类型时，如果假定类型和目标类型之间没有已注册的赋值强制转换，则对于某些类型来说可能已经太晚了。示例：text-

赞成的意见：

• 最低开销

骗局：

• 对于某些类型，类型解析可能会失败。

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表达式慢，正如您在测试中发现的那样。
• 每行的详细语法。

...
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相反。这避免了过早的类型解析。

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

赞成的意见：

• 通常快于1.（甚至0.）
• 具有许多列且只有少数列的表的简短语法。
• 您只需要知道表的相关列名。

骗局：

• 仅适用于少数行的详细语法

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;

您显然知道表名。如果您还知道列数及其顺序，您可以使用它。

对于PostgreSQL中的每个表，都会自动注册一个行类型。如果匹配表达式中的列数，则可以强制转换为表的行类型（'（1,50，）：：foo），从而隐式指定列类型。在逗号后面不加任何内容以输入NULL值。为每个不相关的尾随列添加逗号
在下一步中，您可以使用演示的语法访问各个列。有关字段选择的更多信息，请参见手册。

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

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

赞成的意见：

• 最快（至少在我的测试中，行和列很少）

骗局：

• 从记录/行/复合类型中选择字段的语法并不广为人知

比如3。，但对于标准语法中的分解行：

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

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

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

利与弊，如3。，但使用更常见的语法
而且你需要拼出列名（如果你需要的话）。

就像Unril评论的那样，我们可以结合2的优点。和4。要仅提供列的子集，请执行以下操作：

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

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

• 所有列的批量更新

4.和5.是我的最爱。

分贝