2

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) and 8 (8 keeps children 9-13)
  • changing the paths of ids 3 (3 keeps children 4-14) and 15 (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?

1
  • I don't think swapping can be any easier than that. Oct 4 at 4:15

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.