SQL で連番をふる

背景

Prisma で既存のテーブルへ not null & unique 制約のついた priority カラムを追加した。
このカラムは優先順位を表す int 型のカラムで 1 からのユニークな連番を降る想定。
追加するテーブルにはレコードが登録されているので、マイグレーションするためには事前に値を連番で降っておく必要があった。

以下のテーブルを

次のようにしたい

解決方法

WITH 句を使って以下のような「更新対象のレコードの id を保持する id カラム」と「連番の値を保持する priority カラム」を持つ CTE を作成する。
priority は window 関数の row_number を使うことで連番になるようにしている。

あとは update from を使って CTE から同じ id を持つレコードの priority を使って更新する。

WITH new_priority_table AS (
  SELECT
    id,
    (
      row_number() OVER ()
    ) AS priority
  FROM
    foo
)
UPDATE
  foo
SET
  foo.priority = new_priority_table.priority
FROM
  new_priority_table
WHERE
  foo.id = new_priority_table.id;

補足

なお、今回は連番の順番は何でも良かったので over () の条件は何も指定していませんので、結果がランダムになる可能性があります。
連番の順番を指定したい場合は over (order by name) など条件を指定する必要があります。

参考

• 9.21. ウィンドウ関数 | PostgreSQL 9.6.5文書