Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

A single row can be inserted like this:

client.query("insert into tableName (name, email) values ($1, $2) ", ['john', '[email protected]'], callBack)

This approach automatically comments out any special characters.

How do i insert multiple rows at once?

I need to implement this:

"insert into tableName (name, email) values ('john', '[email protected]'), ('jane', '[email protected]')"

I can just use js string operators to compile such rows manually, but then i need to add special characters escape somehow.

share|improve this question
    
Is there a reason you cannot simply execute INSERT twice? – vitaly-t Jan 25 at 10:32
    
regarding to pg documentation this approach is very undesirable due to performance decrease – stkvtflw Jan 25 at 10:38
    
If executing 2 inserts instead of 1 will danger the performance of your application, then node-postgres, isn't for you at all. But I believe you are looking at it the wrong way, trying to optimize where you shouldn't. This library can insert 10,000 records in under 1 second easily. – vitaly-t Jan 25 at 10:47
    
was the answer satisfactory in your case? If so, please accept it. – vitaly-t Apr 5 at 20:11
up vote 2 down vote accepted

Following this article: Performance Boost from pg-promise library, and its suggested approach:

// Concatenates an array of objects or arrays of values, according to the template,
// to use with insert queries. Can be used either as a class type or as a function.
//
// template = formatting template string
// data = array of either objects or arrays of values
function Inserts(template, data) {
    if (!(this instanceof Inserts)) {
        return new Inserts(template, data);
    }
    this._rawDBType = true;
    this.formatDBType = function () {
        return data.map(d=>'(' + pgp.as.format(template, d) + ')').join(',');
    };
}

An example of using it, exactly as in your case:

var users = [['John', 23], ['Mike', 30], ['David', 18]];

db.none('INSERT INTO Users(name, age) VALUES $1', Inserts('$1, $2', users))
    .then(data=> {
        // OK, all records have been inserted
    })
    .catch(error=> {
        // Error, no records inserted
    });

And it will work with an array of objects as well:

var users = [{name: 'John', age: 23}, {name: 'Mike', age: 30}, {name: 'David', age: 18}];

db.none('INSERT INTO Users(name, age) VALUES $1', Inserts('${name}, ${age}', users))
    .then(data=> {
        // OK, all records have been inserted
    })
    .catch(error=> {
        // Error, no records inserted
    });

UPDATE

For a high-performance approach via a single INSERT query see Multi-row insert with pg-promise.

share|improve this answer
client.query("insert into tableName (name, email) values ($1, $2),($3, $4) ", ['john', '[email protected]','john', '[email protected]'], callBack)

doesn't help? Futher more, you can manually generate a string for query:

insert into tableName (name, email) values (" +var1 + "," + var2 + "),(" +var3 + ", " +var4+ ") "

if you read here, https://github.com/brianc/node-postgres/issues/530 , you can see the same implementation.

share|improve this answer

One other way using PostgreSQL json functions:

client.query('INSERT INTO table (columns) ' +
        'SELECT m.* FROM json_populate_recordset(null::your_custom_type, $1) AS m',
        [JSON.stringify(your_json_object_array)], function(err, result) {
      if(err) {
            console.log(err);
      } else {
            console.log(result);
      }
});
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.