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)


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.





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

  • Does anything prevent you from casting your values to text, do a replace and then cast back to ltree? Like SELECT replace('a.b.c'::ltree::text, 'b', 'd')::ltree; Feb 2, 2015 at 13:25
  • no, I'm free to use text too!
    – Sazid
    Feb 2, 2015 at 13:26
  • But, there's a catch. Say, some row has this value: Test_Case_Create.Case. When you replace Case with something else, the row will have the value: Test_X_Create.X which is not what I want. I want it to become Test_Case_Create.X
    – Sazid
    Feb 2, 2015 at 13:28
  • In an x.y.x.z ltree it does not make much sense to replace both x. Typically you'd want to replace a subpath anchored at left by another subpath, for example x.y.x.* by x.y.t.* (as opposed to x by t) Feb 2, 2015 at 18:13
  • Well it's not a x.y.x.z tree. Its just that, one label i.e x maybe inside of the word (or more specifically a label) y
    – Sazid
    Feb 2, 2015 at 18:31

2 Answers 2


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')) || 
where section_path ~ '*.Test_Case_Creation.*{0}';
  • Yes, this is a nice solution with ltree-only functionality. Mine is a bit less advanced but more readable :) Feb 3, 2015 at 9:20

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.

  • Hey thanks for such detailed answer. I'll let you know if I was successful and have your answer as accepted. BTW, can you tell me what lquery is? And what does ~ operator exactly do? Thanks again :)
    – Sazid
    Feb 2, 2015 at 16:04
  • 2
    I cannot, but the docs can ;) postgresql.org/docs/9.4/static/ltree.html Feb 2, 2015 at 16:07

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.