Skip to content

JSONB bug in int casted query.

Hello, I'm using your node for postgres integration.

Simple inserts and selects work fine, but I have a more complex query to increment a field with a number that's failing.

table = product

column = data

quantity = 1 on initial insert, and increment by one if it exists based on a prior condition check in my flow.

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

The above works fine in the pgsql shell.

With variables for the relevant items in Node-RED...

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

...I get:

image

I verified that the query is correctly generated coming out of a template node...

image

If I pass the template node query through mydb with {{msg.payload}}, it seems that I get a silent failure:

image

Edited by Robert Clayton