使用pg-format构造PostgreSQL批量UPDATE语句时遭遇类型不匹配问题的技术求助

你遇到的错误根源在于使用%L格式化整个嵌套数组时，pg-format会把所有内容都转成字符串字面量——包括数字和布尔值，这就导致PostgreSQL把nv.ordering识别为text类型，而你的表列是smallint，自然会抛出类型不匹配的错误。

正确的实现方式 #

我们需要针对每个元组里的不同类型字段，选择合适的格式化符，再拼接成合法的VALUES子句：

1. 处理每个元组：对字符串类型的photoname用%L（转义字符串字面量，避免SQL注入），对数字类型的ordering和布尔类型的profile用%s（直接插入原始值，保留类型特性）。
2. 拼接元组字符串：把每个格式化后的元组用逗号连接，形成VALUES的内容。
3. 构造完整UPDATE语句：把拼接好的元组字符串插入到SQL模板中。

完整代码示例：

const format = require('pg-format');

// 正确的嵌套数组格式（JS里没有原生元组，嵌套数组是合法的批量数据存储方式）
const myNestedArray = [ 
  [ "ddb73491-b582-4ff5-afb4-e57e31917b03", 1, true ], 
  [ "9cd57b09-17ff-4b88-b08f-b1ef02ec8c1d", 2, false ] 
];

// 格式化每个元组为SQL合法的字面量
const valueTuples = myNestedArray.map(([photoname, ordering, profile]) => 
  format('(%L, %s, %s)', photoname, ordering, profile)
).join(', ');

// 构造最终的UPDATE语句
const updateQuery = format(`
  UPDATE dating.photos 
  SET ordering = nv.ordering, profile = nv.profile 
  FROM (VALUES %s) AS nv (photoname, ordering, profile) 
  WHERE photos.photoname = nv.photoname
`, valueTuples);

为什么这样能解决问题？ #

生成的SQL会是这样的（简化版）：

UPDATE dating.photos 
SET ordering = nv.ordering, profile = nv.profile 
FROM (VALUES ('ddb73491...', 1, true), ('9cd57b...', 2, false)) AS nv (photoname, ordering, profile) 
WHERE photos.photoname = nv.photoname

这里的1、2是数字字面量，true、false是布尔字面量，PostgreSQL能自动匹配对应的列类型，不会再出现类型转换错误，同时也通过%L保证了字符串的安全转义，避免SQL注入风险。

额外提醒 #

你之前尝试的“包含元组的数组格式”在JavaScript里是无效的——(a,b,c)会被解析成逗号表达式，最终数组里的元素只会是每个元组的最后一个值（比如true、false），所以一定要用嵌套数组来存储批量更新的数据。

内容的提问来源于stack exchange，提问作者Adam