Normalize SORT_ORDER column of a user-sortable Postgres table after DELETE/INSERTs

normalize_sort_order.sql

CREATE TABLE tbl (
  id INT,
  sort_order INT
);

WITH updated_order(id, new_sort_order) AS (
    SELECT
      id,
      row_number() OVER (ORDER BY tbl.sort_order NULLS FIRST)
    FROM tbl
)
UPDATE tbl
SET sort_order = updated_order.new_sort_order
FROM updated_order
WHERE tbl.id = updated_order.id
  AND tbl.sort_order IS DISTINCT FROM updated_order.new_sort_order;