带有独立的VALUES表达式 PostgreSQL 不知道数据类型应该是什么。对于简单的数字文字，系统很乐意假设匹配的类型。但对于其他输入（例如NULL）你需要显式地进行转换 - 正如你已经发现的那样。
您可以查询pg_catalog（快速，但特定于 PostgreSQL）或information_schema（缓慢但标准的 SQL）找出并使用适当的类型准备语句。
或者你可以使用这些简单的“技巧”之一（我保存了最好的last):
0. 选择行LIMIT 0，附加行UNION ALL VALUES
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阻止它添加实际行。后续行被强制为现在定义良好的行类型 - 并立即检查它们是否与该类型匹配。应该是对原始形式的微妙的额外改进。
在提供价值的同时all表的列此简短语法可用于第一行：
(TABLE foo LIMIT 0)
Major 局限性：Postgres 转换独立的输入文字VALUES立即表达为“尽力而为”类型。当它稍后尝试转换为第一个的给定类型时SELECT，如果假定类型和目标类型之间没有注册的赋值转换，那么对于某些类型来说可能已经太晚了。例子：text -> timestamp or text -> json.
Pro:
- 最小开销。
- 可读、简单、快速。
- 您只需要知道表的相关列名即可。
Con:
1. 选择行LIMIT 0，附加行UNION ALL SELECT
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;
Pro:
Con:
-
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, 20, NULL)
, (2, 50, NULL)
) t (pkid, x, y) -- columns names not defined yet, only types.
...
与之相反0.这可以避免过早的类型解析。
中的第一行VALUES表达式是一行NULL定义所有后续行的类型的值。该主要噪声行被过滤WHERE f.pkid = t.pkid后来，所以它再也见不到天日了。出于其他目的，您可以使用以下命令消除添加的第一行OFFSET 1在子查询中。
Pro:
- 通常比1.（甚至0.)
- 具有许多列但只有少数列相关的表的简短语法。
- 您只需要知道表的相关列名即可。
Con:
3. 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;
您显然知道表名。如果您还知道列数及其顺序，则可以使用它。
对于 PostgreSQL 中的每个表，都会自动注册一个行类型。如果您匹配表达式中的列数，则可以转换为表的行类型（'(1,50,)'::foo) 从而隐式分配列类型。逗号后面不加任何内容即可输入NULL价值。为每个不相关的尾随列添加逗号。
在下一步中，您可以使用演示的语法访问各个列。更多关于领域选择在手册中.
或者你可以add一行 NULL 值并对实际数据使用统一语法：
...
VALUES
((NULL::foo)) -- row of NULL values
, ('(1,20,)') -- uniform ROW value syntax for all
, ('(2,50,)')
...
Pro:
- 最快（至少在我的测试中，行和列很少）。
- 适用于需要所有列的少数行或表的最短语法。
- 您不必拼写表的列 - 所有列都会自动具有匹配的名称。
Con:
- 从记录/行/复合类型中选择字段的语法不太为人所知。
- 您需要知道默认顺序中相关列的数量和位置。
4. VALUES表达与分解的行类型
Like 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
或者，再次使用前导行 NULL 值：
...
VALUES
((NULL::foo).*) -- row of NULL values
, (1, 20, NULL) -- uniform syntax for all
, (2, 50, NULL)
...
优点和缺点 like 3.，但具有更常见的语法。
并且您需要拼写出列名称（如果需要）。
5. VALUES具有从行类型获取的类型的表达式
Like 温里尔评论道，我们可以结合以下优点2. and 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;
优点和缺点 like 4.，但我们可以使用列的任何子集，而不必知道完整列表。
还显示简短语法UPDATE本身对于有很多列的情况很方便。有关的：
4.和5.是我的最爱。
数据库小提琴here- 展示所有
