Repository navigation
Pass in an array of data [bulk insert] using an insert statement #880
Description
Hi,
How can I pass in an array of 1,000 elements and have pg do a bulk insert into the database?
I am trying to solve this problem brought up here, but using postgres instead
var sql = 'insert into '+table+
' (Month, Merchant_Id, Merchant_Descriptor, Network, Qualification_Code, Transaction_Type, '+
' Issuer_Type, Card_Type, Txn_Count, Txn_Amount, ' +
'Interchange) values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)';
psql.connect();
reduceArray(large_array, function(small_array){
db(small_array);
});
var reduceArray = function(arr, cb){
var size = 1000,
small_array = []
;
for (var i=0; i<arr.length; i+=size){
var chunk = arr.slice(i,i+size);
small_array.push(chunk);
}
cb(small_array);
};
db insert:
var db = function(small_array){
async.times(small_array.length, function(n, next){
var data = small_array[n];
psql.query(sql, [data], function(err,result){
next(err, result);
});
}, function(err){
console.log(err);
});
};
Activity
skilbjo commentedon Oct 24, 2015
Btw, the error I receive with the above code:
brianc commentedon Oct 26, 2015
In your
db insertfunction you're passing inThe
dataparameter there is already an array, you're wrapping it in another array. That array goes in as a single parameter - just pass thedatawithout the array brackets & it should work.skilbjo commentedon Oct 26, 2015
Thanks for the response! If I do that, here's the error message I receive,
Here's what the data structure looks like upon calling that function,
And this is the SQL statement,
skilbjo commentedon Oct 27, 2015
From the postgres documentation, it seems postgres supports this... (http://www.postgresql.org/docs/9.4/static/sql-insert.html)
"To insert multiple rows using the multirow VALUES syntax:"
How can I write the
psql.query(sql, data, function(err,result) {};to replicate this?Here is a small script that replicates the issue...
That script errors:
In PSQL to create the database/table:
brianc commentedon Oct 27, 2015
You have to build the query with placeholders for every single
VALUES($1, $2, $3), VALUES ($4, $5, $6)statement. You might wanna just use anasyncflow control library versus building the single statement, butthat's up to you and the constraints of your app.
On Tue, Oct 27, 2015 at 3:00 PM, John Skilbeck [email protected]
wrote:
skilbjo commentedon Oct 27, 2015
Ok sounds good. Yeah, the way I did it was just using
.map... I thought that this might create unnecessary overhead however (singleinsertstatement for each record)...Was thinking there might be savings if you could just have one
insertstatement with an array of records. Aninsertstatement for each record also works though; was just trying to save fractions of a second.brianc commentedon Oct 27, 2015
Yeah if you really need maximum speed you can string concatenate all the
values into a single query - but absolutely do not do that for
untrusted input. String concatenation is 99% of the time bad, bad, bad for
sql.
You could also look at https://github.com/brianc/node-pg-copy-streams but
it also does not sanitize the input. It doesn't suffer from the same
sql injection attacks because it doesn't use a sql statement to do inserts,
but there could still be issues w/ malicious input.
On Tue, Oct 27, 2015 at 4:18 PM, John Skilbeck [email protected]
wrote:
alex-eri commentedon Mar 25, 2024
For maximum speed use COPY statements
digiperfect-tech commentedon Oct 19, 2025
Most online docs seem to suggest node-postgres doesn't support COPY statements - but all of them are pretty old. Does the library support COPY as of now?