Question: Is it possible to use pg.helpers.ColumnSet to skip updating value based on column's new value

[rakeshriv]

Hi,

I have a requirement where I want to skip updating the column's value if after the update its value goes below 0. I tried looking up pg-promise's documentation but haven't found anything which serves my purpose. Basically I would want to do something like below (I know that the 'skip' part is incorrect but just posting here for clarifying my requirement).

const decrementQuotaColumnSet = new pgp.helpers.ColumnSet([
    skipCol('user_id'),
    {
        name: 'remaining_quota',
        skip(col: any) {
            return 'remaining_quota -' + col.value < 0
        },
        init(col: any) {
            return 'remaining_quota - ' + col.value
        },
        mod: ':raw'
    },
    { name: 'updated_at', def: Date.now() }
], { table: 'user_sms_fup' })

Environment

Version of pg-promise: 11.2.0
Version of Node.js: v16

[vitaly-t]

The above code should work, apart from the skip logic, which in case of a negative value becomes 'remaining_quota -true' and positive - 'remaining_quota -false'. The skip function is supposed to return a boolean, not a string.

Note that skip works only for single-row updates. It does not work for multi-row updates (as documented), because PostgreSql does not support any multi-row skip logic.

[rakeshriv]

I was perhaps not clear about my requirement. I will rephrase the ask for better clarity.
I want to skip updating 'remaining_quota' column if the current value in 'remaining_quota' column minus the new input value is less than 0.
The precedence order of operator '-' is higher than that of the relational operator '<' so it should have worked as it is if at all this thing is supported today.
For e.g if current value of column is 0 and the incoming value is 1 then 0-1 < 0 should result in column getting skipped for an update.