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.