Postgres UPDATE using rank window function

Question

I have a table called `medias` where I've just recently added a new column to called `sort_order` of type `Int`. The row values will not be unique to the entire table, but to their own respective `owner_user_id` fields. Regardless, I don't even care about the uniqueness of them tbh. The point of all this is to allow users to set the sort order of the photos they upload (up to 10, and they can drag and re-order, etc). When the user "deletes" a photo, I don't remove the record, I simply set a `visible` field to `false` on that row. Aaaanyway, I'm introducing a migration that adds `sort_order` (they used to not be able to order the photos, they would just be sorted according to `order by created_at asc`). Since adding the new field, I've made the new `sort_order` have a default value of `10` (so that it's backwards compatible for folks who haven't updated the application). I was able to come up with this query: ``` select owner_user_id, sort_order, rank() over (PARTITION BY owner_user_id…

Accepted Answer

As you do not have a unique key, use `ctid`:

```
update medias m
    set sort_order = new_sort_order
    from (
        select 
            ctid,
            owner_user_id, 
            sort_order, 
            row_number() over w as new_sort_order 
        from medias 
        where visible
        window w as (partition by owner_user_id order by sort_order asc, created_at asc)
    ) s
    where m.ctid = s.ctid;
```

Note, `row_number()` may be better than `rank()` as the first never gives duplicates.

Postgres UPDATE using rank window function

Solution

Problem