1

I ran into an issue with this update query:

UPDATE RankingPosition rp SET rp.position = rp.position + 1 WHERE rp.ranking = :ranking AND rp.position >= :position

I have specified a unique constraint on the position column. The query violates the unique constraint.

I am wondering what I can do to get around this. It is obvious that the final state of the UPDATE would not violate the unique constraint. Unfortunately it looks like the constraint is validated after each individual update, not after the whole update statement is executed.

Is there any way I can overcome this issue?

I already checked that it is working when I remove the unique constraint, and yes, the positions are unique afterwards.

hyperion
  • 119
  • 5
  • *Obvious that the final state of the UPDATE would not violate the unique constraint*. No it is not obvious and it is actually wrong. Having a unique constraint on `position` means any row table cannot have a value of current_row `position+1`. Please You need provide a [mre]. – Belayer Feb 13 '23 at 18:46
  • 1
    Does this answer your question? [Incrementing with one query a set of values in a field with UNIQUE constraint, Postgres](https://stackoverflow.com/questions/5363385/incrementing-with-one-query-a-set-of-values-in-a-field-with-unique-constraint-p) – Henning Feb 14 '23 at 15:29

0 Answers0