PostgreSQL用临时表更新主表报需显式类型转换错误如何解决

错误根因 #

你创建临时表时未显式指定字段类型，PostgreSQL会根据VALUES传入的值自动推断类型，和主表binance_tradika_signals的对应字段类型不匹配，触发类型比较报错：

• 最常见的不匹配字段为id：临时表中id被推断为text/varchar类型，而主表id为bigint/int/numeric数值类型，WHERE t.id = binance_tradika_signals.id执行时没有对应跨类型的比较运算符，触发报错。
• 也可能存在其他字段类型不匹配，比如日期类字段、数值类字段推断类型和主表不一致。

修复方案 #

方案1：创建临时表时显式指定和主表一致的字段类型 #

直接参考主表的表结构创建临时表，避免类型推断错误，示例SQL如下：

-- 直接复制主表的表结构创建临时表，不会复制数据
CREATE TEMP TABLE temp_binance_tradika_signals (LIKE binance_tradika_signals);

-- 再向临时表插入数据
INSERT INTO temp_binance_tradika_signals
VALUES
('100100104895', '2021-07-08 22:59:15', null, null, null, null, null, null, null, 'ALIVE', '2021-07-08 22:59:08', 'Tradika.net', 'Binance', 'SL', 'JIMP_20210708225908_KEYUSDT', 'JIMP_20210708225908_KEYUSDT_SL', 'KEYUSDT_SL', 'KEY', 'USDT', 'KEYUSDT', 'Buy', 0.5, 0.1, 1, 0, 0.000001, 6, 60, '2021-07-08 23:00:15', 0.6, 0.6, null, 1, 0, 0.007369, null, '2021-07-08 22:59:15', 'SIGNAL', 'JIMP', 'Jim Panda Signals', 441, null, null, null, null, null, null, null, null, null, null, 0, 0.007369, 0, 0.007369, 0, 0.007369, -2.5, 0.007185, '1795', null, 600, null, null, -1, 0.007296, null, null, 43200, null, null, -2.3, 0.0072, -2.1, 0.007215, -6, 0.006927, null, 0.25, 0.007388, null, null, 345600),
('100100104896', '2021-07-08 22:59:15', null, null, null, null, null, null, null, 'ALIVE', '2021-07-08 22:59:08', 'Parsika.net', 'Binance', 'MP', 'JIMP_20210708225908_KEYUSDT', 'JIMP_20210708225908_KEYUSDT_MP', 'KEYUSDT_MP', 'KEY', 'USDT', 'KEYUSDT', 'Buy', 1, 0.2, 1, 0, 0.000001, 6, 100, '2021-07-08 23:00:55', 1, 1, null, 1, 0, 0.007369, null, '2021-07-08 22:59:15', 'SIGNAL', 'JIMP', 'Jim Panda Signals', 441, null, null, null, null, null, null, null, null, null, null, 0, 0.007369, 0, 0.007369, 0, 0.007369, -10, 0.006633, '1944', null, 600, null, null, -4, 0.007075, null, null, 86400, null, null, -9, 0.006706, -8.5, 0.006743, -15, 0.006264, null, 0.25, 0.007388, null, null, 345600);

这种方式是最优解，能从根源避免所有字段类型不匹配问题，后续更新操作不需要修改。

方案2：临时修改更新语句添加强制类型转换 #

如果不想改动临时表创建逻辑，可以在更新的WHERE条件里对类型不匹配的字段做显式转换，示例（假设主表id是bigint类型）：

UPDATE binance_tradika_signals
SET 
signal_state = t.signal_state,
price_latest_price = t.price_latest_price
FROM temp_binance_tradika_signals t
WHERE t.id::bigint = binance_tradika_signals.id;

如果存在其他字段赋值时类型不匹配，也可以对应添加转换，比如t.price_latest_price::numeric之类的，根据主表实际字段类型调整即可。

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