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

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)

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 :)

One can view the ltree data type as a simple text with some constraints. The most important of these constraints is that a label is bordered by dots if part of a label path. Obviously, if it stands at either end, one dot will be missing; when alone, the dots are completely missing.

Fortunately, casting between the two types is easy. This way we can set up a small set of rules for handling replacement, using lquery to find the cases:

-- 1. replace when the label is the whole labelpath
replace(section_path::text, 'to_be_replaced', 'replacement') 
    WHERE section_path ~ 'to_be_replaced'::lquery

-- 2. replace when the label is at the end
replace(section_path::text, '.to_be_replaced', '.replacement')
    WHERE section_path ~ '*.to_be_replaced'::lquery

-- 3. replace when the label is at the beginning
replace(section_path::text, 'to_be_replaced.', '.replacement')
    WHERE section_path ~ 'to_be_replaced.*'::lquery

-- 4. replace when the label is in the middle
replace(section_path::text, '.to_be_replaced.', '.replacement.')
    WHERE section_path ~ '*.to_be_replaced.*'::lquery

In cases 2-4, mind the dots in the search and replacement values of replace(). This way you can be sure you always replace the whole label, not just parts of it.

StackExchange Database Administrators Q#90797, answer score: 2