I'm using the PDO library in PHP and I'm generating a PostgreSQL statement like this:
INSERT INTO production_work (order_id, producer_id, sw)
VALUES (?,?,?)
ON CONFLICT (order_id, producer_id, day)
DO UPDATE SET sw = EXCLUDED.sw + ?
RETURNING ident
and for the parameters to the PDO execute() statement I'm passing in the array [7172, 10, 1, 1].
That production_work table has a default value for the day parameter of now() which is why I don't pass it in, and a unique constraint set on the three fields listed for the ON CONFLICT part.
When there is no conflict, it's correctly setting the sw column's value to 1. However, when there is a conflict, and the value of that sw column is currently 0, then after this statement runs the sw column ends up with a value of 2, not 1.
What am I doing wrong?
The record EXCLUDED contains values which were to be inserted but caused the conflict, so EXCLUDED.sw = 1 in this case. There is no need to add to it another 1:
INSERT INTO production_work (order_id, producer_id, sw)
VALUES (?,?,?)
ON CONFLICT (order_id, producer_id, day)
DO UPDATE SET sw = EXCLUDED.sw
RETURNING ident;
If you want to add a given value to the existing one use:
INSERT INTO production_work (order_id, producer_id, sw)
VALUES (?,?,?)
ON CONFLICT (order_id, producer_id, day)
DO UPDATE SET sw = sw + EXCLUDED.sw
RETURNING ident;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments