Using the row_number() window function to reorder images

One of my customers uses the rails acts_as_list module for maintaining order of images on forms.

Through some application issues they ended up with images that had identical order for the same provider and image_category. So, it ended up looking like this:

select provider_id, image_category_id, position, id from images where image_category_id=1234 order by position;

   

provider_id | image_category_id | position | id

------------+-------------------+----------+--------

       9999 |              1234 |        3 | 484900

       9999 |              1234 |        4 | 484899

       9999 |              1234 |        4 | 484897

       9999 |              1234 |        4 | 484896

       9999 |              1234 |        4 | 484894

       9999 |              1234 |        4 | 484893

       9999 |              1234 |        4 | 484892

       9999 |              1234 |        4 | 484890

       9999 |              1234 |        5 | 484889

       9999 |              1234 |        6 | 484887

       9999 |              1234 |        7 | 484886

       9999 |              1234 |        8 | 479818

       9999 |              1234 |        9 | 479817

       9999 |              1234 |       10 | 479815

Note that there are several rows with position=4 above. This was a prevalent issue throughout the images table and not just with this example provider.

So, what they wanted was to reorder the images such that the equal positions were kept together and the overall order stayed the same.  So, for example, it should look like this (sorry for the small font):

 provider_id |image_category_id | position |   id  

-------------+------------------+----------+-------

        9999 |             1234 |        3 | 484900  ==> Position 1

        9999 |             1234 |        4 | 484899  ==> Position 2

        9999 |             1234 |        4 | 484897  ==> Position 3

        9999 |             1234 |        4 | 484896  ==> Position 4

        9999 |             1234 |        4 | 484894  ==> Position 5

        9999 |             1234 |        4 | 484893  ==> Position 6

        9999 |             1234 |        4 | 484892  ==> Position 7

        9999 |             1234 |        4 | 484890  ==> Position 8

        9999 |             1234 |        5 | 484889  ==> Position 9

        9999 |             1234 |        6 | 484887  ==> Position 10

        9999 |             1234 |        7 | 484886  ==> Position 11

        9999 |             1234 |        8 | 479818  ==> Position 12

        9999 |             1234 |        9 | 479817  ==> Position 13

        9999 |             1234 |       10 | 479815  ==> Position 14

So, I thought about it for a bit, then it occurred to me: why not use the row_number() window function.  So I wrote the following migration SQL script:

BEGIN;

CREATE temp TABLE new_image_positions 

ON COMMIT DROP

AS
   SELECT id
        , row_number AS position
     FROM (
      SELECT provider_type
            , provider_id
            , image_category_id
            , position
            , id
            , row_number()
        OVER (
      partition BY provider_id
              , image_category_id
          ORDER BY position
              )
        FROM images
        WHERE provider_type     = 'Project'

AND position IS NOT NULL
          ) x ;

  UPDATE images
    SET position = n.position
    FROM new_image_positions n
  WHERE images.id  = n.id;

COMMIT;

And, voila, all the images are now properly ordered:

 provider_id | image_category_id | position |   id   

-------------+-------------------+----------+--------

        9999 |              1234 |        1 | 484900

        9999 |              1234 |        2 | 484899

        9999 |              1234 |        3 | 484897

        9999 |              1234 |        4 | 484896

        9999 |              1234 |        5 | 484894

        9999 |              1234 |        6 | 484893

        9999 |              1234 |        7 | 484892

        9999 |              1234 |        8 | 484890

        9999 |              1234 |        9 | 484889

        9999 |              1234 |       10 | 484887

        9999 |              1234 |       11 | 484886

        9999 |              1234 |       12 | 479818

        9999 |              1234 |       13 | 479817

        9999 |              1234 |       14 | 479815

<SNIP>

        9999 |              1234 |       46 | 403052

        9999 |              1234 |       47 | 403051

        9999 |              1234 |       48 | 403050

        9999 |              1234 |       49 | 403049

        9999 |              1235 |        1 | 484916

        9999 |              1235 |        2 | 484915

        9999 |              1235 |        3 | 484912

        9999 |              1235 |        4 | 484910

        9999 |              1236 |        1 | 493000

        9999 |              1236 |        2 | 492999