使用 UPDATE 對錶行進行排序並保存行號

我的 Postgres 9.5 有一個movimientos包含以下數據的表：

| 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 |

我需要根據orden欄位對記錄進行編號並將這些數字保留在numero欄位中，因為我需要這些數據numero在報告中進行排序和搜尋。例子：

| 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 |

我嘗試使用帶有 a 的函式來做到這一點，FOR但一百萬行非常慢。

如何使用簡單的方法做到這一點UPDATE？

加入numero使用視窗函式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;

UPDATE手冊中的語法詳細資訊。

如果您具有並發寫訪問權限，則需要鎖定表以避免競爭條件。

請注意，無論哪種方式，更新表中的每一行都是昂貴的。該表通常會增長到其大小的兩倍，VACUUM或者VACUUM FULL可能是有序的。

根據您的完整情況，首先編寫一個新表*可能更有效。*與說明相關的答案：

• 在大表中填充新列的最佳方法？
• 優化 PostgreSQL 中的批量更新性能

但是，我不相信您根本需要numero表格中的列。也許您正在尋找一個MATERIALIZED VIEW. 最近關於 SO 的相關答案：

• 分塊查詢中的全域行號

引用自：https://dba.stackexchange.com/questions/137535