PostgreSQLで表示順番に連番を登録する

概要

小規模なメッセージ管理ツールで並び替えを行いたいとの要望があったので
連番を一括で登録する方法をメモしておく。

要件

• 並び替え対象：表示中のメッセージ（１０件程度）
• ドラッグ＆ドロップで並び替えたい
• 新規登録時に、指定がなければ1番目に登録したい
• 新規登録・更新時に指定した番号に差し込みたい

以上

設計

件数がしれているので、表示順番を変更する場合は、
シンプルに表示中のメッセージすべてに表示順番を振り直す仕様とする。

表示順番の振り直し方法

postgresで表示順番などのカラムに連番を設定する。

対象テーブル(sample)

• ID (id) ※ 主キー
• タイトル (title)
• メッセージ (message)
• ステータス (status) ※ 1:公開, 2:非公開
• 表示順番 (sort)
• 登録日時 (created_at)
• 更新日時 (updated_at)
• 論理削除フラグ (is_deleted)

一括更新するSQL

UPDATE
  sample AS t1
SET
  sort = rank
FROM (
  SELECT
    id,
    ROW_NUMBER() OVER (ORDER BY sort IS NOT NULL, sort ASC, updated_at DESC) AS rank
  FROM
    sample
  WHERE 
    status = 1 
    AND is_deleted = false
) AS t2
WHERE 
  t1.id = t2.id
;