How do I properly insert multiple rows into PG with node-postgres?

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.

Answers:

Answer

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-1

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

UPDATE-2

The information here is quite old now, see the latest syntax for Custom Type Formatting. What used to be _rawDBType is now rawType, and formatDBType was renamed into toPostgres.

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

Answer

Use pg-format npm like below.

var format = require('pg-format');

var values = [[7, 'john22', '[email protected]', '9999999922'], [6, 'testvk', '[email protected]', '88888888888']];
client.query(format('INSERT INTO users (id, name, email, phone) VALUES %L', values),[], (err, result)=>{
    console.log(err);
    console.log(result);
});

Tags

Recent Questions

Top Questions

Home Tags Terms of Service Privacy Policy DMCA Contact Us

©2020 All rights reserved.