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

stkvtflw picture stkvtflw · Jan 25, 2016 · Viewed 21.4k times · Source

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.

Answer

Sergey Okatov picture Sergey Okatov · May 11, 2016

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