Skip to content

Instantly share code, notes, and snippets.

@s-andringa
Last active August 29, 2015 14:01
Show Gist options
  • Save s-andringa/65d023027ce1431850bf to your computer and use it in GitHub Desktop.
Save s-andringa/65d023027ce1431850bf to your computer and use it in GitHub Desktop.
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)
-- 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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment