Postgresql - 使用临时表"You might need to add explicit type casts"更新表时出现问题

Question

我正在使用node.js开发一个应用程序。我的数据库是PostgreSQL。我尝试创建一个临时表，然后用那个临时表更新我的主表。原因是我有许多记录和数据必须每秒更新一次。我知道一个必须有效的方法是，首先根据您的数据创建一个临时表，然后用临时表更新主表。我成功地创建了临时表，但当我尝试更新我的主表时；CCD_ 1"；错误有谁能帮助我或提示我如何解决这个问题？我的临时创建sql是：

CREATE TEMP TABLE temp_binance_tradika_signals
(id, thpars_dati, thpars_vise, thpars_aipi, thpars_code, thpars_site, thpars_owne, thpars_list, thpars_prio, signal_state, signal_date, signal_account, signal_exchange, signal_abbrev, signal_id, signal_uid, signal_andis, signal_coin, signal_base, signal_symbol, signal_side, signal_step_log, signal_step_update, signal_lotstep, signal_lotint_step, signal_pricetick, signal_priceint_tick, signal_time_init, signal_date_alive, signal_dca_buy, signal_dca_sell, signal_amount_type, signal_score, open_percent, open_price, open_amount, open_date, open_trigger, open_prv_abbrev, open_prv_title, open_prv_code, close_percent, close_price, close_amount, close_date, close_trigger, close_period, close_result, close_prv_abbrev, close_prv_title, close_prv_code, price_latest_percent, price_latest_price, price_higest_percent, price_higest_price, price_lowest_percent, price_lowest_price, point_buy_percent, point_buy_price, point_buy_amount, point_buy_date, point_buy_lifelong, point_buy_lifetime, point_buy_bool, point_sell_percent, point_sell_price, point_sell_amount, point_sell_date, point_sell_lifelong, point_sell_lifetime, point_sell_bool, point_place_percent, point_place_price, point_eject_percent, point_eject_price, point_stoploss_percent, point_stoploss_price, point_stoploss_bool, point_expire_percent, point_expire_price, point_expire_bool, point_expire_date, point_expire_duration) AS
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)
;

我从临时表中更新的SQL是：

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 = binance_tradika_signals.id;

任何帮助都将不胜感激。

Answer 1

解决方案是：

1. id必须以整数而非字符串形式出现
2. insert into temp表的第一行数据指定数据类型。例如，如果将signal_step_log作为JSON数据类型插入到临时表中，如果将此行定义为Varchar、int、Bool等。。。。你会得到一个错误

希望这会有用。