9

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?

1 Answer 1

10

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:

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.