Renumber a position column which is scoped to another column, useful before adding a unique index / null constraint. (Keywords: Postgres, SQL, Reorder scoped position column, row_number in UPDATE clause)

renumber_position_column_postgres.sql

-- Table in question:
CREATE TABLE product_tags (
    id integer NOT NULL,
    product_id integer NOT NULL,
    tag_id integer NOT NULL,
    "position" integer
);

-- Renumber the position column based on row number in partition:
UPDATE product_tags pt
SET position = numbered.nr
FROM (
      SELECT id, row_number() over(
            PARTITION BY product_id
            ORDER BY position NULLS LAST
            -- Orders records where position IS NULL at end of list
            -- Use NULLS FIRST to prepend these records to start of list
      ) AS nr
      FROM product_tags
) AS numbered
WHERE pt.id = numbered.id;

-- Now you can safely add a unique index to (product_id, position)
-- and a NOT NULL constraint to position.