tl;dr

Sometimes, when unique values will be user-managed and may need changing around, prefer a deferred unique constraint to a plain unique index.

Discussion

Today I was working on a common ask in web development - have a list of items where items can be reordered freely with drag-and-drop.

Focusing on the back-end, I was thinking how to manage the changes in order data (seq column). Turns out there are several approaches depending on amount of data and consistency needs. If there are millions of items, avoiding the need to update many of them can make floating-point seq useful. In my case, however, there will only ever be a handful of items, so I went down the simplest unique integer sequence route.
Surprisingly, it turned out to have a few gotchas of its own. Adding a simple unique index on seq in combination with relative mass update a-la items.update_all("seq = seq + #{offset}") was resulting in uniqueness violation errors, and a simple "swap seq 1 with seq 2" scenario seemed impossible, even in a transaction.
Turns out, an index's uniqueness is checked after every update, even in a transaction, but for my use-case a check only at the end of a transaction, so, deferred contraint to the rescue!

execute <<~SQL
  ALTER TABLE items
  ADD CONSTRAINT unique_seq
  UNIQUE (seq)
  DEFERRABLE INITIALLY DEFERRED;
SQL