SQL: How do I update a value inside of an ltree?

Question

As the title suggests, I'm having a hard time, guessing how I can efficiently update a value in multiple rows. The column is implemented with the data-type ltree. So, there should be some form of special operations available for this data type in postgresql Here's an example:

Name of the table, let's say: product_sections and the name of the column containing these values is section_path (type: ltree)

Android
Android.Browser
Android.Browser.Test
Android.Browser.Compare.URL

Here, I want to update only the Browser to something like Foo part so that, the data will become like this:

Android
Android.Foo
Android.Foo.Test
Android.Foo.Compare.URL

Thanks in advance :)

Saponin

Answer 1

update product_sections
set product_path = 'Android.foo'::lpath|| subpath(product_sections,2)) 
where product_path <@ 'Android.browser'::lpath

untested, but this should achieve the correct result.

Cental

I just saw a syntax error on the last line. so be sure to use the latest version I just fixed. – Cental

Answer 2

For dynamic results, you can use nlevel function as a subpath parameter (untested):

update product_sections
set product_path = 'Android.foo'::lpath|| subpath(product_sections,nlevel('Android.foo'))) 
where product_path <@ 'Android.browser'::lpath

Oligosaccharide

In Basic this answer is correct but there are a few mistakes. I think this is what was meant: UPDATE product_sections SET product_path = 'Android.foo' || subpath(product_path, 2, nlevel(product_path)-2) WHERE product_path <@ 'Android.browser' AND nlevel(product_path)>2; – Vomer

Answer 3

I had the same need in my project. The previous answers didn't work for me so I'll post my solution.

I've found a way to do it in two requests : the first one modify the ltree path of Android.Browser to Android.Foo and the second one modify the ltree paths for the children of Android.Browser.

Here they are :

UPDATE product_sections SET product_path = 'Android.Foo'
       WHERE path = 'Android.Browser';
UPDATE product_sections SET product_path = 'Android.Foo'
       || subpath(product_path, nlevel('Android.Browser')) 
       WHERE product_path ~ 'Android.Browser.*{1,}';

Kantor

Answer 4

The above error lies in Incorrect using function which transfer text to ltree. we should use text2ltree(),the sql is

update tree_test
set path = text2ltree('Android.foo') || subpath(path,2))
where product_path <@ 'Android.browser';

and the url is https://www.postgresql.org/docs/9.4/ltree.html

Mucilage

Answer 5

I realize I am a little late to the party on this one. Upon my research, I found there really is no good way and simple way to do this. I created the following user defined function that will get the job done

SELECT '44.22.23'::TEXT::LTREE AS orginal
      , ltree_swap('44.22.23'::TEXT::LTREE,'23'::TEXT::LTREE,'fish'::TEXT::LTREE,2) AS swap

 --=========RESULT SET=========--
-- orginal    swap
-- 44.22.23   44.22.fish

Entirely