How to update a table in postgres to add an order column?

Question

I can do a window function like select *, rank() over (partition by column1 order by column2) from mytable to get the order of the rows sorted by column2. But if I've added a column order_column to mytable, how can I UPDATE that column to include the results of the window function, so that the new order_column gets populated with the results?

So it would update all the existing rows in mytable but it wouldn't add any new rows.

Answer 1

You probably should not be doing this, as your order column is really just data derived from the rest of the table, and it could be invalidated upon the change of any data. That being said, if you must do this, an update join would be one option:

UPDATE mytable AS t1
SET order_column = t2.rnk
FROM
(
    SELECT *, RANK() OVER (PARTITION BY column1 ORDER BY column2) rnk
    FROM mytable
) t2
WHERE t1.id = t2.id;

I assume here that your table has a primary key column id. If the PK be named something else, change the name used in my query. If your table doesn't have a primary key, then add one.