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

score 3 · Answer 1 · answered Feb 01 '15 at 06:00

3

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.

answered Feb 01 '15 at 06:00

Joe Love

5,594
2
20
32

I just saw a syntax error on the last line. so be sure to use the latest version I just fixed. – Joe Love Feb 01 '15 at 06:04

score 2 · Answer 2 · edited May 16 '17 at 01:29

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

edited May 16 '17 at 01:29

Pang

9,564
146
81
122

answered May 16 '17 at 01:24

Frederico

21
4

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;` – DeniseMeander May 24 '23 at 12:25

score 1 · Answer 3 · answered Nov 05 '18 at 14:34

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,}';

score 1 · Answer 4 · answered Jun 12 '19 at 12:24

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

score 0 · Answer 5 · answered Nov 14 '19 at 18:58

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

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

5 Answers5