Question: Multi-insert statement

[espringe]

Postgres supports something like:
INSERT INTO foo(a,b) VALUES (q,r), (s,t) ...

How can I do this in node-postgres? Right now I'm using a transaction, and async.parallelLimit for individual INSERTs. But this seems rather inefficient, and it's as I need it for a reasonably high-volume log -- where every few seconds I need to insert quite a bit of data -- performance matters.

So I'm wondering if there's a better way. (And I happy to string concat my own query, but I don't an escaping function exposed)

[benighted]

You can build the query in that format just like any other INSERT statement, but for your case I think a prepared statement would be the best option for performance and you will not need to escape anything. Check it out: https://github.com/brianc/node-postgres/wiki/Prepared-Statements

[espringe]

Oh, I wasn't clear. The problem is a I have a dynamic amount of rows I need to insert. There's no way for me ahead of time to know how many there is. E.g. I'm inserting between 1 (most typical) and 500 (very rarely) rows of data at a time.

[brianc]

You can still do this with a prepared statement, you'll just have to build up the parameters and append them all to the query...something like:

var rows = [{
  name: 'Brian',
  age: 31
}, {
  name: 'Aaron',
  age: 29
}]

var buildStatement = function(rows) {
  var params = []
  var chunks = []
  for(var i = 0; i < rows.lenght; i++) {
    var row = rows[i]
    var valuesClause = []
    params.push(row.name)
    valueClause.push('$' + params.length)
    params.push(row.age)
    valueClause.push('$' + params.length)
    chunks.push('(' + valueClause.join(', ') + ')')
  }
  return {
    text: 'INSERT INTO foo(name, age) VALUES ' + chunks.join(', '),
    values: params
  } 
}

client.query(buildStatement(rows))

Something like that. I wrote it off the top of my head. There are also a myriad of query builders for node which should help make this much easier.

[piccoloaiutante]

This is something working:

var rows = [{
  name: 'Brian',
  age: 31
}, {
  name: 'Aaron',
  age: 29
}]

function buildStatement (insert, rows) {
  const params = []
  const chunks = []
  rows.forEach(row => {
    const valueClause = []
    row.forEach(p => {
      params.push(p)
      valueClause.push('$' + params.length)
    })
    chunks.push('(' + valueClause.join(', ') + ')')
  })
  return {
    statement: insert + chunks.join(', '),
    params: params
  }
}

client.query(buildStatement('INSERT INTO foo(name, age) VALUES ', rows))

[kethinov]

The above needs a slight tweak in the middle to actually work:

var rows = [{
  name: 'Brian',
  age: 31
}, {
  name: 'Aaron',
  age: 29
}]

function buildStatement (insert, rows) {
  const params = []
  const chunks = []
  rows.forEach(row => {
    const valueClause = []
    Object.keys(row).forEach(p => {
      params.push(row[p])
      valueClause.push('$' + params.length)
    })
    chunks.push('(' + valueClause.join(', ') + ')')
  })
  return {
    text: insert + chunks.join(', '),
    values: params
  }
}

client.query(buildStatement('INSERT INTO foo(name, age) VALUES ', rows))

FYI for anyone reading the thread, buildStatement returns the following SQL:

{ statement: 'INSERT INTO foo(name, age) VALUES ($1, $2), ($3, $4)',
  params: [ 'Brian', 31, 'Aaron', 29 ] }

[karthikeyabhaghavatula]

Thank you very much Kethinov, it works

[Blquinn]

JS object keys and objects are not guaranteed to be ordered. @kethinov 's answer is not safe to use as the tuples could be inserted in a random order, breaking the SQL statement.

It would be advisable to also supply an array of keys from the object that you'd like to include in the statement, or something to that effect.

As always, correct me if I'm wrong and sorry for the late reply.

[charmander]

JS object keys and objects are not guaranteed to be ordered.

As of ES6, they are ordered.

[Blquinn]

https://stackoverflow.com/questions/30076219/does-es6-introduce-a-well-defined-order-of-enumeration-for-object-properties

This SO answer claims that Object.keys and for-in do not guarantee order even in ES6. The implementation of v8 could change as it is not in the spec.

[charmander]

Oh, yep, that’s true. Sorry. Reading the context, the query shouldn’t be built dynamically anyway; use a fixed number of arrays. #1320 (comment)

[BrainTonic]

Thank you for the buildStatement! It saved me so much time. Just wanted to share something that I added to it so I can have it return rows with the query.

function buildStatement (insert, rows, returnFieldArr=[]) {
    const params = []
    const chunks = []
    // If returnFieldArr is not null, we add params to allow returning
    // fields that are listed in the array. If null, returning statement isn't added.
    let returning = ''
    if (returnFieldArr.length !== 0) {
        returning = ' RETURNING ' + returnFieldArr.toString();
    }
    rows.forEach(row => {
        const valueClause = []
        Object.keys(row).forEach(p => {
            params.push(row[p])
            valueClause.push('$' + params.length)
        })
        chunks.push('(' + valueClause.join(', ') + ')')
    })
    return {
        text: insert + chunks.join(', ') + returning,
        values: params
    }
}

Here's something that also helped out:

function buildInsert(theDATA, section) {
    // 'section' maps to what table to insert into
    // the data is the JSON data
    var statement = 'INSERT INTO ' + section + '( submitted_file_fk, ';
    var field_keys = Object.keys(theDATA[section][0]);
    var length_of_keys = field_keys.length;
    for (var i = 0; i < length_of_keys; i++) {
        statement += field_keys[i];
        if (i !== length_of_keys - 1) {
            statement += ', ';
        }
    }
    statement += ') VALUES ';
    return statement;
}

[nmjmdr]

Another version:

function buildMultiInsert(table, columns, dataRows) {
  const header = "insert into " + table + ' (' +columns.join(',') +') values ';
  return {
    text: header + buildParameters(columns, dataRows),
    values: buildValues(columns, dataRows)
  }
}

function buildParameters(columns, dataRows) {
  return dataRows.map(function(row,rowIndex){
    return'(' + columns.map((c, i)=>{
      return "$" + ((rowIndex * columns.length) + i+1)
    }).join(', ') + ')'
  }).join(', ')
}

function buildValues(columns, dataRows) {
   const valuesArray = dataRows.map(function(row,rowIndex){
    return columns.map(function(column){
      console.log(column, row[column])
      return row[column]
    })
  })
  return [].concat.apply([], valuesArray);
}