How do I swap two record's paths/positions in an ltree using postgres, where all children follow? There is a uniqueness constraint on the path.
Given the following records on a pages table:
id: 1, path: "1",
id: 2, path: "1.1",
id: 3, path: "2",
id: 4, path: "2.1",
id: 5, path: "2.2",
id: 6, path: "2.2.1",
id: 7, path: "2.2.1.1",
id: 8, path: "2.3",
id: 9, path: "2.3.1",
id: 10, path: "2.3.1.1",
id: 11, path: "2.3.1.2",
id: 12, path: "2.3.1.3",
id: 13, path: "2.3.1.4",
id: 14, path: "2.4",
id: 15, path: "3",
id: 16, path: "3.1",
id: 17, path: "3.2",
id: 18, path: "3.2.1",
id: 19, path: "3.2.2",
id: 20, path: "3.2.3",
I can move record 15 (and children 16-20) to anywhere in the tree using the following:
-- $1 is newpath, $2 is oldpath
UPDATE "pages" SET "path" = $1 WHERE "pages"."id" = 15;
UPDATE "pages" SET path = $1 || subpath(path, nlevel($2)) WHERE (pages.path <@ $2) AND (pages.path != $1)
But what is the best way to to swap the positions of two records (and children)? For example:
- changing the paths of ids
5(5 keeps children 6-7) and8(8 keeps children 9-13) - changing the paths of ids
3(3 keeps children 4-14) and15(15 keeps children 16-20)
I saw this post, where the author swaps a value of two rows with a temporary value (in their case they negate). Should I do a similar thing, but using the query from before three times? Is there a better way which doesn't require 6 UPDATE calls?
