Help with ::int inside a ::jsonb casted query

[RNCTX]

Hey folks, I'm using IBM's Node RED tool to mock up a few concepts and in multiple Postgres packages for that platform that use pg, am able to reproduce a jsonb update query issue that leads me to think I have a syntax problem I can't find a solution to. I'm using Node v. 12.13.0

I have a table "product" with a jsonb column "data", without indexes other than "pk" the serialized primary key. The data column has a property "quantity" that I initially insert with value "1" and am trying to conditionally increment based on a check of its existence in the flow of the application.

The query...

UPDATE product SET data = data || ('{"quantity": ' || ((data->>'quantity')::int + 1) || '}')::jsonb WHERE pk = 1;

...works fine in the pgsql shell, but sent over via pg it fails with:

This plugin requires pg 7.11.0, this one requires 7.18.2, and both return the same error when trying to execute the above query with the table and pk pulled in from prior function variables.

I tried using the recommendation here to change the query to...

UPDATE product SET data = jsonb_set(data || ('{"quantity": ' || jsonb_build_object((data->'quantity')::int + 1) || '}')::jsonb) WHERE pk = 1;

But I get the same error.

Any help appreciated!

[charmander]

Can you reproduce the problem just using pg with Node?

[charmander]

Also, applying the recommendation you linked to would look like this:

UPDATE product
    SET data = data || jsonb_build_object('quantity', (data->'quantity')::int + 1)
    WHERE pk = 1

[RNCTX]

Using pg@7.18.2 (the one my most recent app plugin required) and your docs example:

const { Pool, Client } = require('pg')
const client = new Client({
  user: 'xxxxxxx',
  host: 'localhost',
  database: 'xxxxxx',
  password: 'xxxxxxx',
  port: 5432,
})
client.connect()
client.query(`UPDATE product SET data = data || jsonb_build_object('quantity', (data->'quantity')::int + 1) WHERE pk = 4;`, (err, res) => {
  console.log(err, res)
  client.end()
})

I get the same type casting error with your query...

nodered@NAS:~/.node-red$ node query.js
error: cannot cast type jsonb to integer
    at Connection.parseE (/var/www/nodered/.node-red/node_modules/pg/lib/connection.js:614:13)
    at Connection.parseMessage (/var/www/nodered/.node-red/node_modules/pg/lib/connection.js:413:19)
    at Socket.<anonymous> (/var/www/nodered/.node-red/node_modules/pg/lib/connection.js:129:22)
    at Socket.emit (events.js:210:5)
    at addChunk (_stream_readable.js:308:12)
    at readableAddChunk (_stream_readable.js:289:11)
    at Socket.Readable.push (_stream_readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:182:23) {
  name: 'error',
  length: 104,
  severity: 'ERROR',
  code: '42846',
  detail: undefined,
  hint: undefined,
  position: '84',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_expr.c',
  line: '2735',
  routine: 'transformTypeCast'
} undefined

With my original syntax, the query runs fine...

const { Pool, Client } = require('pg')
const client = new Client({
  user: 'xxxxxxx',
  host: 'localhost',
  database: 'xxxxxx',
  password: 'xxxxxxx',
  port: 5432,
})
client.connect()
client.query(`UPDATE product SET data = data || ('{"quantity": ' || ((data->>'quantity')::int + 1) || '}')::jsonb WHERE pk = 4;`, (err, res) => {
  console.log(err, res)
  client.end()
})

nodered@NAS:~/.node-red$ node query.js
null Result {
  command: 'UPDATE',
  rowCount: 1,
  oid: null,
  rows: [],
  fields: [],
  _parsers: undefined,
  _types: TypeOverrides {
    _types: {
      getTypeParser: [Function: getTypeParser],
      setTypeParser: [Function: setTypeParser],
      arrayParser: [Object],
      builtins: [Object]
    },
    text: {},
    binary: {}
  },
  RowCtor: null,
  rowAsArray: false
}

nodered@NAS:~/.node-red$ node query.js
null Result {
  command: 'UPDATE',
  rowCount: 1,
  oid: null,
  rows: [],
  fields: [],
  _parsers: undefined,
  _types: TypeOverrides {
    _types: {
      getTypeParser: [Function: getTypeParser],
      setTypeParser: [Function: setTypeParser],
      arrayParser: [Object],
      builtins: [Object]
    },
    text: {},
    binary: {}
  },
  RowCtor: null,
  rowAsArray: false
}

postgres=# \c nodered
You are now connected to database "nodered" as user "postgres".
nodered=# select data->'quantity' from product;
 ?column? 
----------
 1
 1
 1
 5
(4 rows)

nodered=# select data->'quantity' from product;
 ?column? 
----------
 1
 1
 1
 6
(4 rows)

nodered=#

So it looks like I need to hit up IBM's issue reports, it's not pg's fault.

Thanks for the reply/help!