Pg-promise: multiple update with helpers fails when trying to update column type timestamp

[Juanflugel]

I am trying to implement a multi-rows update using the helpers.update method but when trying to update the column type time-stamp the transaction fails if i remove this column the query works as expected.
I already tried using any possible date-time format but it does not work. updating a time-stamp in a single row sending a String (date) works.

Have someone any idea how to fix this situation?

function updateItemsStates (req, res, next) {
    let arrayOfItems = req.body;
    console.log(typeof(arrayOfItems[0].state_date));
    db.tx(t =>{     
        let cs = new pgp.helpers.ColumnSet(['?iip_id','state_id','user_id','state_date'], {table: 'items_in_projects'});
        let query = pgp.helpers.update(arrayOfItems, cs) + ' WHERE v.iip_id = t.iip_id RETURNING *';

        return t.any(query)
        .then(data0 =>{
            console.log(data0);
            let css = new pgp.helpers.ColumnSet(['iip_id','state_id','user_id'], {table: 'items_in_projects_states'});
            let queryItemsInProjectsStates = pgp.helpers.insert(data0,css) + 'RETURNING iip_id';
            return t.any(queryItemsInProjectsStates);
        })
        return t.batch(results); 
    })
    .then(data =>{
        res.json({
            status:'success',
            data:data
        });
    })
    .catch(error =>{
        return next(error);
    });
}

[
    {"iip_id":2,"state_id":2,"user_id":1,"state_date":"2017-07-24 18:56:27"},
    {"iip_id":3,"state_id":2,"user_id":2,"state_date":"2017-07-24 18:56:27"},
    {"iip_id":4,"state_id":3,"user_id":1,"state_date":"2017-07-24 18:56:27"},
    {"iip_id":6,"state_id":2,"user_id":3,"state_date":"2017-07-24 18:56:27"}
]

[vitaly-t]

I don't know what type you are using in your table, but you can always re-cast the type. Use the column like this: {name: 'state_date', cast: 'date'}, or use whatever type you need casting into (see [Column]).

Also, do not create ColumnType dynamically, they are heavy static structures, as documented everywhere, and meant to be created once.

And lastly, your call return t.batch(results); is pointless, it is never even reached.

[Juanflugel]

Hi Vitaly, Thanks a lot for soon and good answer, casting the data solved the problem. I also want to say that this library you wrote is just Great. this is my first experience with SQL (Postgres) before I worked with MongoDB but now I need to Change to a Relational DB. I have been using pg-promise for the last two months and it is so simple to use, so well documented and you are always there to give support, I just wanted to say thanks for your remarkable work.

If one day I can help you in any way, Just ask for.
Greetings From Germany
Juan.

[vitaly-t]

You are welcome! :)