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:
I verified that the query is correctly generated coming out of a template node...
If I pass the template node query through mydb with {{msg.payload}}
, it seems that I get a silent failure: