sql – 更新多行时转换NULL类型

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

表

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,(null,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,1)更改为(null：int,2)但我不能这样做,因为我有一个生成这些“更新”的函数多行“查询,它不知道有关列类型的任何信息.

什么是最好的解决方案？是否有更好的多行更新查询？是否有任何函数或语法如AS t(x：gettype(foo.x),y：gettype(foo.y),pkid：gettype(foo.pkid))？

解决方法

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

或者你可以使用这些简单的“技巧”之一(我最后保存了最好的)：

1.选择LIMIT 0的行,用UNION ALL追加行

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,NULL
   UNION ALL
   SELECT 2,NULL
   ) t               -- column names and types are already defined
WHERE  f.pkid = t.pkid

子查询的第一个子选择：

(SELECT x,pkid  FROM foo LIMIT 0)

获取列的名称和类型,但LIMIT 0阻止它添加实际行.后续行被强制转换为现在明确定义的行类型 – 如果它们与类型匹配,则立即检查.应该是对原始形式的微妙的额外改进.

主要限制：使用单独的SELECT行,Postgres立即将输入文字转换为“尽力而为”类型.当它稍后尝试转换为第一个SELECT的给定类型时,如果在假定类型和目标类型之间没有注册的赋值转换,则某些类型可能已经太晚了.示例文本 – >时间戳.

优点：
– 最小开销.
– 可读,简单,快速,几行.
– 您只需要知道表的相关列名.

缺点：
– 某些类型的类型解析可能会失败.
– 对于长行列表,UNION ALL SELECT比VALUES表达式慢,正如您在测试中找到的那样.
– 每行详细语法.

2.每列类型的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,NULL),(2,NULL)
   ) t (pkid,y)  -- columns names not defined yet,only types.
...

VALUES表达式中的第一行是一行NULL值,用于定义所有后续行的类型.

优点：
– 比1快.
– 具有许多列的表的最短语法,只有少数几个是相关的.
– 您只需要知道表的相关列名.

缺点：
– 只有几行的详细语法
– 不太可读(IMO).

3.具有行类型的VALUES表达式

UPDATE foo f
SET x = (t.r).x       -- parenthesis needed to make syntax unambiguous,y = (t.r).y
FROM (
   VALUES
      ('(1,)'::foo)  -- columns need to be in default order of table,('(2,)')       -- nothing after the last comma for NULL
   ) t (r)              -- column name for row type
WHERE  f.pkid = (t.r).pkid

你显然知道表名.如果您也知道列数及其顺序,则可以使用它.

对于PostgreSQL中的每个表,都会自动注册行类型.如果匹配表达式中的列数,则可以强制转换为表的行类型(‘(1,)’:: foo),从而隐式分配列类型.不要在逗号后面输入任何值来输入NULL值.为每个不相关的尾随列添加逗号.
在下一步中,您可以使用演示的语法访问各个列.更多关于Field Selection in the manual.

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

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

更新的行将被UPDATE中的WHERE子句排除.
出于其他目的,您可以在子查询中消除添加OFFSET 1的第一行.

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

缺点：
– 从记录/行/复合类型中选择字段的语法不是很清楚.
– 您需要按默认顺序了解相关列的数量和位置.

4. VALUES表达式与分解的行类型

像3.,但标准语法中的分解行：

UPDATE foo f
SET    x = t.x,y = t.y
FROM (
   VALUES
      (('(1,)'::foo).*)  -- decomposed row of values,NULL)
   ) t(pkid,y)  -- arbitrary column names (I made them match)
WHERE  f.pkid = t.pkid;

或者,再次使用前导行的NULL值：

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

优点和缺点如3.,但有更常见的语法.
你需要拼出列名(如果你需要的话).

5.从行类型获取的VALUES表达式类型

与Unril commented一样,我们可以结合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,y)       -- arbitrary column names (I made them match)
WHERE  f.pkid = t.pkid;

优点和缺点如4.,但我们可以使用任何列的子集,而不必知道完整列表.

还显示UPDATE本身的短语法,方便多列的情况.有关：

> Bulk update of all columns

4.和5.是我的最爱.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!