2

I have a need to insert into a table and ensure that one column is being incremented each time.

My insert looks like:

INSERT INTO my_tbl (id) VALUES ($1)
 ON CONFLICT (id) DO UPDATE SET total = my_tbl + 1 RETURNING *;

(where total = an integer field that defaults to 0)

Assuming I don't have any rollbacks or anything going on, is it safe to assume that multiple executions of this statement with the same parameters at once would be locked and ordered safely such that each execution would return (given the RETURNING * bit) three different values in the total field?

My apologies for the phrasing of this question and its title as I'm not sure how to categorize what I'm trying to do.

1
  • 1
    I suppose you mean INSERT INTO my_tbl AS t (id) VALUES (1) ON CONFLICT (id) DO UPDATE SET total = t.total + 1 RETURNING *;? Oct 20, 2022 at 8:17

1 Answer 1

1

Yes, you can safely assume that. Concurrent updates to a single row are serialized by FOR UPDATE or FOR NO KEY UPDATE row locks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.