How to insert multiple rows using node-postgres?

Question

This is my statement:

INSERT INTO userpermissions (username, permission)
VALUES ($1, $2),($3,$4)
RETURNING *;

This is my code:

db.query(stmt2,values2, (err2, result2) => {
       //Do other stuff depending on if there is an error or result
}

Where stmt2, is the above statement, and values2 is an array which I create by doing:

            var values2 = [];
            for(i=0;i<permissions.length;i++){
                values2.push(username);
                values2.push(permissions[i]);
            }

There is some kind of error / fatal exception, but I can't see it because the domain of the error is wrapped.

You absolutely should use a single insert query for that. Here's an example: https://stackoverflow.com/questions/37300997/multi-row-insert-with-pg-promise — vitaly-t, Oct 11 '17 at 09:51

score 4 · Accepted Answer · answered Oct 11 '17 at 09:50

sql:

f=# create table tt(i int,t text);
CREATE TABLE
f=# grant all on tt to t;
GRANT

js:

const { Pool, Client } = require('pg')

const client = new Client({
  user: 't',
  host: '10.10.10.10',
  database: 'f',
  password: 't',
  port: 5432
})
client.connect()

client.query('INSERT INTO tt(i, t) VALUES($1, $2),($3,$4) RETURNING *', ['1', 'SO',2,'sO'], (err, res) => {
  console.log(err, res)
  client.end()
})

run:

C:\Users\Vao\vatest>node t.js
null Result {
  command: 'INSERT',
  rowCount: 2,
  oid: 0,
  rows: [ anonymous { i: 1, t: 'SO' }, anonymous { i: 2, t: 'sO' } ],
  fields:
   [ Field {
       name: 'i',
       tableID: 131471390,
       columnID: 1,
       dataTypeID: 23,
       dataTypeSize: 4,
       dataTypeModifier: -1,
       format: 'text' },
     Field {
       name: 't',
       tableID: 131471390,
       columnID: 2,
       dataTypeID: 25,
       dataTypeSize: -1,
       dataTypeModifier: -1,
       format: 'text' } ],
  _parsers: [ [Function: parseInteger], [Function: noParse] ],
  RowCtor: [Function: anonymous],
  rowAsArray: false,
  _getTypeParser: [Function: bound ] }

finally check:

f=# select * from tt;
 i | t
---+----
 1 | SO
 2 | sO
(2 rows)

How to insert multiple rows using node-postgres?

1 Answers1