Postgresql – How to update the parent/child of all rows in the tree (ltree)

postgresqlpostgresql-9.3update

Here are my configurations:
DB type: postgresql (9.3 if that's necessary)
Table name: product_sections
Column names: section_id (integer) & section_path (ltree)

table_image

Detail:
I have one reference value: section_id: 15 & section_path: Automation_Solutionz. So, when I want to rename Automation_Solutionz to, say Chrome, I want all references of Automation_Solutionz to be updated.

FROM:

Automation_Solutionz
Automation_Solutionz.Test_Case_Creation
Automation_Solutionz.Test_Case_Creation.Automation
Automation_Solutionz.Test_Case_Creation.Manual

TO:

Chrome
Chrome.Test_Case_Creation
Chrome.Test_Case_Creation.Automation
Chrome.Test_Case_Creation.Manual

Similarly, when I update a value which is in the middle, it should rename those too. In the case above, Test_Case_Creation should be renamed to something like TestCase or anything else. The same goes for any item which is either in the middle, end or at the start.

Hope, I explained things correctly. Thanks in advance 🙂

Best Answer

Another approach to achieve this ....

If the replaced text is only one level tree

update product_sections 
set section_path = 'Chrome' 
where section_path = 'Automation_Solutionz'

If the replaced text is at the beginning

update product_sections 
set section_path = 'Chrome' || subpath(section_path,1) 
where section_path ~ 'Automation_Solutionz.*'

If the replaced text is in the middle

update product_sections 
set section_path = subpath(section_path, 0, index(section_path, 'Test_Case_Creation')) || 
                   'Testcase' || 
                   subpath(section_path, index(section_path, 'Test_Case_Creation') + 1) 
where section_path ~ '*.Test_Case_Creation.*{1,}';

If the replaced text is at the end

update product_sections 
set section_path = subpath(section_path, 0, index(section_path, 'Test_Case_Creation')) || 
                   'Testcase' 
where section_path ~ '*.Test_Case_Creation.*{0}';
Related Question