Peter Peter - 1 year ago 70
Node.js Question

PostgreSQL multi-row updates in Node.js

As i already found here On Stackoverflow it is possible to update multiple rows in one query by doing something like this

update test as t set
column_a = c.column_a,
column_c = c.column_c
from (values
('123', 1, '---'),
('345', 2, '+++')
) as c(column_b, column_a, column_c)
where c.column_b = t.column_b;

special thanks to @Roman Pekar for the clear answer.

Now i'm trying to merge this way of updating with querying to a postgreSQL database in NodeJS.

Here is a snipped of my code:

var requestData = [
{id: 1, value: 1234}
{id: 2, value: 5678}
{id: 3, value: 91011}

client.connect(function (err) {
if (err) throw err;

client.query(buildStatement(requestData), function (err, result) {
if (err) throw err;


client.end(function (err) {
if (err) throw err;

var buildStatement = function(requestData) {
var params = [];
var chunks = [];

for(var i = 0; i < requestData.length; i++) {

var row = requestData[i];
var valuesClause = [];

valuesClause.push('$' + params.length);
valuesClause.push('$' + params.length);

chunks.push('(' + valuesClause.join(', ') + ')');


return {
text: 'UPDATE fit_ratios as f set ratio_budget = c.ratio_budget from (VALUES ' + chunks.join(', ') + ') as c(ratio_label, ratio_budget) WHERE c.ratio_label = f.ratio_label', values: params

i don't get an error but it doesn't update my table, i don't really know what goes wrong here. Perhaps a syntax error in my query code? I just don't find any specific examples of multiple row querying when updating in NodeJS pg package

Answer Source

The example below is based on library pg-promise, and its method helpers.update:

// library initialization, usually placed in its own module:
var pgp = require('pg-promise')({
    capSQL: true // capitalize all generated SQL

var db = pgp(/*your connection details*/);

// records to be updated:
var updateData = [
    {id: 1, value: 1234},
    {id: 2, value: 5678},
    {id: 3, value: 91011}

// declare your ColumnSet once, and then reuse it:
var cs = new pgp.helpers.ColumnSet(['?id', 'value'], {table: 'fit_ratios'});

// generating the update query where it is needed:
var update = pgp.helpers.update(updateData, cs) + ' WHERE =';
//=> UPDATE "fit_ratios" AS t SET "value"=v."value"
//   FROM (VALUES(1,1234),(2,5678),(3,91011))
//   AS v("id","value") WHERE =

// executing the query:
    .then(()=> {
        // success;
    .catch(error=> {
        // error;

This method of generating multi-updates can be characterized as:

  • very fast, as it relies on type ColumnSet that implements smart caching for query generation
  • completely safe, as all data types are going through the library's query formatting engine to make sure everything is formatted and escaped correctly.
  • very flexible, due to advanced ColumnConfig syntax supported for the columns definition.
  • very easy to use, due to the simplified interface implemented by pg-promise.

Note that we used ? in front of column id to indicate that the column is part of the condition, but not to be updated. For complete column syntax see class Column and ColumnConfig structure.

Related question: Multi-row insert with pg-promise.