sqlpostgresqlltree

Postgres ltree module, update id of a node with all the children related paths

I have the following simple table:

create table nodes(
    id text primary key,
    path ltree
);

lets say I put some data to the table:

insert into nodes (id, path) values ('A', 'A');
insert into nodes (id, path) values ('B', 'A.B');
insert into nodes (id, path) values ('C', 'A.C');

so that the tree looks like:

       A

  /          \

B             C

Now I want to rename id of A to, lets say, X so that the tree 

       X

  /          \

B             C

and the table would look like 

insert into nodes (id, path) values ('X', 'X');
insert into nodes (id, path) values ('B', 'X.B');
insert into nodes (id, path) values ('C', 'X.C');

Could someone please give a hint - is that possible to do with a single query?

Would appreciate any help, thanks

Solution

  • Using the approach from http://patshaughnessy.net/2017/12/14/manipulating-trees-using-sql-and-the-postgres-ltree-extension, specifically the "Moving branch" section:

    UPDATE nodes SET id = 'X' WHERE id = 'A';
UPDATE nodes SET path = 'X' || subpath(path, 1) WHERE path <@ 'A';