UPDATE statement on UPSERT not incrementing value
I have the following QUERY:
INSERT INTO ReservationCounter (student_id, reservation_count) VALUES (1, 1) ON CONFLICT (student_id) DO UPDATE SET reservation_count=excluded.reservation_count+1;
When there is not a conflict it
When there is a conflict, however, the
UPDATEstatement doesn't actually updates the value with the increment.
When I run it on conflict the first time it returns 2 as expected, but when I run it again I expect it to increment 2 to 3 (because of
reservation_count=excluded.reservation_count+1). It does not do that, it returns 2 again; leading me to believe that
reservation_countis always 1.
What am I doing wrong?
Laycee last edited by
You are setting reservation_count to the inserted value + 1, not incrementing the current value.
You probably want to add the provided count to the existing count.
SET reservation_count = reservationcounter.reservation_count + excluded.reservation_count;