I'm trying to update large amount of rows (20,000) rows, so i created a temp table similar to this one. and i want to create a trigger on insertion or update and a function to take the inserted or updated data and updated in the B table where identifier column is the unique id in both A and B tables

The structure of both tables will be something like this:

  • user (text)
  • identifier (text)
  • product (text)
  • status (text)
  • sales (numeric)
  • purchase (numeric).

how to write a trigger and a function in postgreSQL to update data in B table on insert or update in A table?

Note I want to insert the data in a temp or normal table AKA(A table) and then do the update in B table with SQL only

  • Why do you have two identical tables to begin with? Why not put everything into one? – a_horse_with_no_name Jun 18 at 23:45
  • @a_horse_with_no_name i made an issue on stake over flow , and i was trying a different approach like mikas suggested , the issue was large amount of queries using kenxjs and nodejs , make a lot of problem causing knex time oute pool is probably full error stackoverflow.com/questions/56634985/… – sasha romanov Jun 18 at 23:47
  • I have no idea what that code is supposed to do. Reading the comments it seems that this will spawn thousands of threads each doing a single update which is crazy (and means those updates are a single transaction each, rather than one transaction which also sounds questionable). Why can't you just run a single UPDATE statement? I don't know Node.js, but can't you use one "prepared statement" (or whatever it is called in JavaScript) and use some kind of batching to send the updates? What are you changing in the table? Is that different for each row? Or the same values for each row? – a_horse_with_no_name Jun 18 at 23:52
  • @a_horse_with_no_name updated values yes it changes every 1 minute , and i searched a lot on how to do bulk update for multiple rows , but i couldn't find anything in knexjs docs – sasha romanov Jun 18 at 23:55
  • @a_horse_with_no_name yes that's why i changed the question, so i can try another approach, in case it couldn't be resolve – sasha romanov Jun 19 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.