SQL批量更新多行时NULL值类型不匹配报错排查

问题原因 #

PostgreSQL解析VALUES子句时会自动推断列的数据类型，当你在VALUES中使用null且未明确指定类型时，数据库会将该列推断为text类型，而目标表的suggestion字段是可空integer类型，因此触发类型不匹配错误。

解决方案 #

方法1：给null明确指定整数类型 #

修改VALUES子句中的null为NULL::integer，强制指定其类型为整数：

UPDATE shipment_item AS ass
SET units_per_box = av.units_per_box,
    boxes = av.boxes,
    box_per_pallet = av.box_per_pallet,
    suggestion = av.suggestion
FROM (VALUES  (20091,36,2,44,NULL::integer)  ) AS av
(id,units_per_box,boxes,
 box_per_pallet,suggestion)

方法2：在定义临时表av时指定列类型 #

声明av的列时，明确指定suggestion为integer类型：

UPDATE shipment_item AS ass
SET units_per_box = av.units_per_box,
    boxes = av.boxes,
    box_per_pallet = av.box_per_pallet,
    suggestion = av.suggestion
FROM (VALUES  (20091,36,2,44,null)  ) AS av
(id integer, units_per_box integer, boxes integer,
 box_per_pallet integer, suggestion integer)

两种方法都能让数据库正确识别suggestion列的类型，消除类型不匹配的错误。

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