How to conditionally update multiple rows using knex with postgresql?

Question

Here is an example of the table layout;

CREATE TABLE test (id1 int, id2 int, auth boolean);
INSERT INTO test VALUES (1, 1, true);

I'm trying to convert the following query into the knex.js framework;

UPDATE test as t 
    SET auth = c.auth
    from (values (1, 1, false),(2, 1, false))
    as c(id1, id2, auth)
    where c.id1 = t.id1 AND c.id2 = t.id2;

select * from test

Here is a fiddle: http://sqlfiddle.com/#!17/62529/8

I've looked around, and found the following resources: github issue, update multiple queries (no conditional)

After trying to implement these methods, I'm still coming up unsuccessful and not sure where I'm going wrong.

I've tried to brute force the query by wrapping my original postgres query into a knex.raw statement, like:

return knex.raw('' +
    'UPDATE test as t ' +
    'SET auth = c.auth ' +
    'from (values (1, 1, false),(2, 1, false))' +
    'as c(id1, id2, auth)' +
    'where c.id1 = t.id1 AND c.id2 = t.id2;')

which comes up with the error syntax error on or near « as »

I've also tried to follow the github issue advice by using;

 let string = knex
        .into('test t')
        .update('auth, c.auth')
        .values('(1, 1, false), (2, 1, false)')
        .where(knex.raw('c.id1 = t.id1 AND c.id2 = t.id2'))
        .toString()

        knex.raw(string)

which returns the error 'values' is not a function.

I am new to both knex and postgres, so I'm not sure if I'm missing something painfully obvious! Any help is much appreciated.

GaryL · Accepted Answer · 2017-11-17T17:06:05.037

In the raw version it looks like you may need a space before the 'as' at false))as to become false)) as...

I saw this by adding the .on('query-error', as shown below. With this info you should be able to determine whether it's a SQL engine error vs a Knex error, and if the SQL was generated as you desired.

return knex.raw('' +
    'UPDATE test as t ' +
    'SET auth = c.auth ' +
    'from (values (1, 1, false),(2, 1, false)) ' +
    'as c(id1, id2, auth)' +
    'where c.id1 = t.id1 AND c.id2 = t.id2;')
    .on('query-error', function(ex, obj) {
        console.log("KNEX query-error ex:", ex, "obj:", obj);
    }).then(function(retVal) {
        console.log("Query ran okay.");
        return retVal;
    });

There is also .on('query', which will give you info about the query to help get it right. see: http://knexjs.org/#Interfaces-query

Regards, Gary.

How to conditionally update multiple rows using knex with postgresql?

1 Answers1