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 INSERTS successfully.

    When there is a conflict, however, the UPDATE statement 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_count is always 1.

    What am I doing wrong?



  • 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;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2