Sort table rows and save the row numbers using UPDATE

Question

I have Postgres 9.5 with a table movimientos that has the following data:

| id | concepto | movimiento | numero | orden |
|  1 | AJUSTE 1 |       2542 |      0 |     2 |
|  2 | APERTURA |      12541 |      0 |     1 |
|  3 | AJUSTE 2 |       2642 |      0 |     2 |
|  4 | CIERRE   |      22642 |      0 |     3 |

And I need to number the records based on the orden field and keep these numbers in the numero field, because I need this data to sort and search by numero in reports. Example:

| id | concepto | movimiento | numero | orden |
|  2 | APERTURA |      12541 |      1 |     1 |
|  1 | AJUSTE 1 |       2542 |      2 |     2 |
|  3 | AJUSTE 2 |       2642 |      3 |     2 |
|  4 | CIERRE   |      22642 |      4 |     3 |

I tried to do it using a function with a FOR but is very slow with a million rows.

How to do this using a simple UPDATE?

Answer 1

Join to a subquery that computes numero with the window function row_number():

UPDATE movimientos m
SET    numero = sub.rn
FROM  (SELECT id, row_number() OVER (ORDER BY orden, id) AS rn FROM movimientos) sub
WHERE  m.id = sub.id;

Details for UPDATE syntax in the manual.

If you have concurrent write access you need to lock the table to avoid race conditions.

Note that updating every row in a table is expensive either way. The table typically grows to twice its size and VACUUM or VACUUM FULL may be in order.

Depending on your complete situation it may be more efficient to write a new table to begin with. Related answers with instructions:

• Best way to populate a new column in a large table?
• Optimizing bulk update performance in PostgreSQL

I am not convinced, though, that you need the column numero in your table at all. Maybe you are looking for a MATERIALIZED VIEW. Recent related answer on SO:

• Global row numbers in chunked query