ded ded - 1 month ago 9
SQL Question

Postgres UPDATE using rank window function

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 ORDER BY sort_order asc, created_at asc) as new_sort_order
from medias
where visible=true
order by sort_order asc, created_at asc;


This spits out something that looks like the following:

owner_user_id | sort_order | new_sort_order
---------------+------------+---------------
76 | 10 | 1
76 | 10 | 2
76 | 10 | 3
76 | 10 | 4
76 | 10 | 5
9 | 10 | 1
9 | 10 | 2
9 | 10 | 3
9 | 10 | 4
9 | 10 | 5
79 | 10 | 1
79 | 10 | 2
87 | 10 | 1
87 | 10 | 2
87 | 10 | 3
85 | 10 | 1
90 | 10 | 1
90 | 10 | 2
90 | 10 | 3


at this point all i really want to do is set that
sort_order
to that
rank()
. Any thoughts on how to do this?

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.