Currently, supporting a feature like "users can reorder the items in a playlist" is typically done using an integer position column. However, this doesn't prevent gaps (so e.g. the third item in the playlist might have `position = 42`), and inserting between two other items requires updating every row with a greater position value.
I'd like to be able to say `update ... set position = 3` to make the record the third item in the list. You'd need to be able to set the scope (eg `add column position ORDINAL WINDOW BY playlist_id` or something).
Currently, supporting a feature like "users can reorder the items in a playlist" is typically done using an integer position column. However, this doesn't prevent gaps (so e.g. the third item in the playlist might have `position = 42`), and inserting between two other items requires updating every row with a greater position value.
I'd like to be able to say `update ... set position = 3` to make the record the third item in the list. You'd need to be able to set the scope (eg `add column position ORDINAL WINDOW BY playlist_id` or something).