No batch insert and update query support  #956

Activity

1. Dineshagrawal85 commented on Mar 3, 2016

   

   Dineshagrawal85

   on Mar 3, 2016

   More actions

   //yes you can simply do that using bluebird and pg
   var promise = require('bluebird');
   var options = {
   promiseLib: promise
   };
   var pgp = require('pg-promise')(options);
   var db = pgp(cn); //cn is your connection string

   // array is you array of values and first argument to t.query is your query using individual values of array in u
   db.tx(function(t){
   var queries=array.map(function(u){
   return t.query('', u);
   });
   return t.batch(queries);
   })
   .then(function(){
   //res
   })
   .catch(function(error){
   //error response
   });

   👎React with 👎2Reacted by Ferdi Gökdemir and mc-bxw
2. vitaly-t commented on Mar 6, 2016

   

   vitaly-t

   on Mar 6, 2016

   Contributor

   More actions

   Want to pass multiple insert values in to single query. Is there any other way to do it?

   You can concatenate inserts into a single insert query. For example, see here.
3. rpedela commented on Mar 22, 2016

   

   rpedela

   on Mar 22, 2016

   Contributor

   More actions

   I am not aware of a way to batch update into a single query. For batch insert, see #957.

   👍React with 👍1Reacted by Maksim Danilau
4. charmander commented on Jul 27, 2017

   

   charmander

   on Jul 27, 2017

   Collaborator

   More actions

   #957 (comment)
5. charmander

   closed this as completedon Jul 27, 2017
6. sebastianrothbucher commented on Feb 20, 2022

   

   sebastianrothbucher

   on Feb 20, 2022

   Last edited by sebastianrothbucher

   More actions

   case this still helps: you can use temp tables to achieve this - like running those two statements:

   create temp table coffeeTmp on commit drop as select 'Homer' as name, 'latte' as coffee union select 'Marge' as name, 'expresso' as coffee
   
   update coffeeOrders c 
     set coffee=t.coffee
   from coffeeTmp t
   where c.name=t.name
   

   when running via node-postgres, it would become sth like

   c.query('begin')
   c.query('create temp table coffeeTmp on commit drop as select $1 as name, $2 as coffee union select $3 as name, $4 as coffee', ['Homer', 'latte', 'Marge', 'expresso'])
   c.query('update coffeeOrders c  set coffee=t.coffee from coffeeTmp t where c.name=t.name')
   // (more here)
   c.query('commit')
   

   just BTW: insert would work as well - and so would upsert by running above update followed by an insert like

   insert into coffeeOrders (name, coffee)
     select t.name, t.coffee from coffeeTmp t left join coffeeOrders c on t.name=c.name where c.name is null