Postgresql - Problem when update table with temporary table "You might need to add explicit type casts"

Viewed 80

I'm in the middle of developing an app using node.js. my database is PostgreSQL. I tried to create a temporary table and then update my main table with that temporary table. the reason is that i have many records and data that must update every second. i know that one of the must efficient way is that first create a temp table by your data and then update main table with temporary table. i create temp table successfully but when i try to update my main table i catch "No operator matches the given name and argument types. You might need to add explicit type casts" error. anybody can help me or hint me how i can solve this problem? my temporary creation sql is :

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)
;

and my update SQL from temporary table is :

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;

any help will be really appreciated.

1 Answers

The solution is :

  1. id must present as integer not string.
  2. the first row data of insert into temp table specify the datatype. so for example if you insert signal_step_log into temp table as JSON datatype, if you define this row as Varchar, int, Bool etc .... you will get an error.

Hope this will be useful.

Related