Pass in an array of data [bulk insert] using an insert statement

[skilbjo]

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

[skilbjo]

Btw, the error I receive with the above code:

{ [error: bind message supplies 1 parameters, but prepared statement "" requires 11]
  name: 'error',
  length: 131,
  severity: 'ERROR',
  code: '08P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postgres.c',
  line: '1524',
  routine: 'exec_bind_message' }

[brianc]

In your db insert function you're passing in

psql.query(sql, [data], function(err,result){
next(err, result);
});

The data parameter there is already an array, you're wrapping it in another array. That array goes in as a single parameter - just pass the data without the array brackets & it should work.

[skilbjo]

Thanks for the response! If I do that, here's the error message I receive,

{ [error: bind message supplies 1000 parameters, but prepared statement "" requires 11]
  name: 'error',
  length: 134,
  severity: 'ERROR',
  code: '08P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postgres.c',
  line: '1524',
  routine: 'exec_bind_message' }

Here's what the data structure looks like upon calling that function,

 [ 
   [ '20150930', '4445018858357', 'AnotherCafe', 'Discover', 'DS PREPAID PSL CARD NOT PRESENT/ECOMM',  'Gross',  'Domestic',  'Debit',  2,  '144.70',   '2.94' ],
   [ '20150930', '4445018858357', 'AnotherCafe', 'Discover', 'DS PREPAID PSL REAL ESTATE', 'Gross',  'Domestic', 'Debit', 47, '25314.02', '278.67'],
   ...etc...
 ]

And this is the SQL statement,

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

[skilbjo]

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:"

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

How can I write the psql.query(sql, data, function(err,result) {}; to replicate this?

Here is a small script that replicates the issue...

var 
    pg = require('pg'),
    conString = 'postgres://skilbjo:5432@localhost/test',
    psql = new pg.Client(conString),
    data =  [ [ '20150930', '4445018858357', 'AnotherCafe', 'Discover', 'DS PREPAID PSL CARD NOT PRESENT/ECOMM',  'Gross',  'Domestic',  'Debit',  2,  '144.70',   '2.94' ],
       [ '20150930', '4445018858357', 'AnotherCafe', 'Discover', 'DS PREPAID PSL REAL ESTATE', 'Gross',  'Domestic', 'Debit', 47, '25314.02', '278.67'] ],
    sql = 'insert into Vantiv'+
    ' (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();

psql.query(sql, data, function(err,result){
    if(err) console.log(err);
    console.log(result);
});

That script errors:

{ [error: bind message supplies 2 parameters, but prepared statement "" requires 11]
  name: 'error',
  length: 131,
  severity: 'ERROR',
  code: '08P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postgres.c',
  line: '1524',
  routine: 'exec_bind_message' }

In PSQL to create the database/table:

create database Test;

create table Vantiv (
    id serial primary key,
    Month date,
    Merchant_Id varchar(45),
    Merchant_Descriptor varchar(45),
    Network varchar(45),
    Qualification_Code varchar(45),
    Transaction_Type varchar(45),
    Issuer_Type varchar(45),
    Card_Type varchar(45),
    Txn_Count integer,
    Txn_Amount decimal(48,2),
    Interchange decimal(48,2)
);

[brianc]

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 an
async flow control library versus building the single statement, but
that's up to you and the constraints of your app.

On Tue, Oct 27, 2015 at 3:00 PM, John Skilbeck notifications@github.com
wrote:

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:"

INSERT INTO films (code, title, did, date_prod, kind) VALUES
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

How can I write the psql.query(sql, data, function(err,result) {}; to
replicate this?

Here is a small script that replicates the issue...

var
pg = require('pg'),
conString = 'postgres://jskilbeck:5432@localhost/test',
psql = new pg.Client(conString),
data = [ [ '20150930', '4445018858357', 'AnotherCafe', 'Discover', 'DS PREPAID PSL CARD NOT PRESENT/ECOMM', 'Gross', 'Domestic', 'Debit', 2, '144.70', '2.94' ],
[ '20150930', '4445018858357', 'AnotherCafe', 'Discover', 'DS PREPAID PSL REAL ESTATE', 'Gross', 'Domestic', 'Debit', 47, '25314.02', '278.67'] ],
sql = 'insert into Vantiv'+
' (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();

psql.query(sql, data, function(err,result){
if(err) console.log(err);
console.log(result);
});

That script errors:

{ [error: bind message supplies 2 parameters, but prepared statement "" requires 11]
name: 'error',
length: 131,
severity: 'ERROR',
code: '08P01',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'postgres.c',
line: '1524',
routine: 'exec_bind_message' }

In PSQL to create the database/table:

create database Test;

create table Vantiv (
id serial primary key,
Month date,
Merchant_Id varchar(45),
Merchant_Descriptor varchar(45),
Network varchar(45),
Qualification_Code varchar(45),
Transaction_Type varchar(45),
Issuer_Type varchar(45),
Card_Type varchar(45),
Txn_Count integer,
Txn_Amount decimal(48,2),
Interchange decimal(48,2)
);

—
Reply to this email directly or view it on GitHub
#880 (comment)
.

[skilbjo]

Ok sounds good. Yeah, the way I did it was just using .map ... I thought that this might create unnecessary overhead however (single insert statement for each record)...

Was thinking there might be savings if you could just have one insert statement with an array of records. An insert statement for each record also works though; was just trying to save fractions of a second.

data.map(function(item,index){
  psql.query(sql, item, function(err,result){
    if(err) console.log(err);
  });
});

[brianc]

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 notifications@github.com
wrote:

Ok sounds good. Yeah, the way I did it was just using .map ... I thought
that this might create unnecessary overhead however (single insert
statement for each record)...

Was thinking there might be savings if you could just have one insert
statement with an array of records. An insert statement for each record
also works though; was just trying to save fractions of a second.

data.map(function(item,index){
psql.query(sql, item, function(err,result){
if(err) console.log(err);
});
});

—
Reply to this email directly or view it on GitHub
#880 (comment)
.

[alex-eri]

For maximum speed use COPY statements