你好,我正在尝试更新表中的多行,我使用了这个sql,但尽管建议可以为null整数,但我抛出了错误column suggestion is of type integer but expression is of type text
SQL:
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,units_per_box,boxes,
box_per_pallet,suggestion)
在最新的PostgreSQL版本中,VALUES
语句中的任何不可识别常量(如NULL或字符串文字(都将被强制键入text
:
SELECT pg_typeof(c3)
FROM (VALUES (1, 1.2, NULL)) AS a(c1,c2,c3);
pg_typeof
═══════════
text
(1 row)
因此,您应该应用显式类型转换:NULL::integer
。