Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- table with ltree column
CREATE TABLE tbl_ltree(id int, res_id text, path ltree);
INSERT INTO tbl_ltree VALUES
  (1, 'res_1'    , 'res_1')
, (2, 'res_1_1'  , 'res_1.res_1_1')
, (3, 'res_1_2'  , 'res_1.res_1_2')
, (4, 'res_1_1_1', 'res_1.res_1_1.res_1_1_1')
;
-- GiST index for ltree column
CREATE INDEX tbl_path_gist_idx ON tbl_ltree USING GIST (path);

-- table with text column
CREATE TABLE tbl_txt(id int, res_id text, path text);
INSERT INTO tbl_txt VALUES
  (1, 'res_1'    , 'res_1')
, (2, 'res_1_1'  , 'res_1.res_1_1')
, (3, 'res_1_2'  , 'res_1.res_1_2')
, (4, 'res_1_1_1', 'res_1.res_1_1.res_1_1_1')
;
4 rows affected

4 rows affected

-- working with ltree 
SELECT t.*
FROM   tbl_ltree t1   
JOIN   tbl_ltree t ON t.path @> t1.path
WHERE  t1.res_id = 'res_1_1_1';  -- your search term here


id
res_id
path


1
res_1
res_1

2
res_1_1
res_1.res_1_1

4
res_1_1_1
res_1.res_1_1.res_1_1_1


-- working with ltree and without the redundant column res_id:
SELECT *
FROM   tbl_ltree   
WHERE  path @> (SELECT path FROM tbl_ltree WHERE path ~ '*.res_1_1_1'::lquery);  -- your search term here


id
res_id
path


1
res_1
res_1

2
res_1_1
res_1.res_1_1

4
res_1_1_1
res_1.res_1_1.res_1_1_1


-- can use index:
SET enable_seqscan = off;  -- to show for small table

EXPLAIN
SELECT *
FROM   tbl_ltree   
WHERE  path @> (SELECT path FROM tbl_ltree WHERE path ~ '*.res_1_1_1'::lquery);  -- your search term here


QUERY PLAN


Index Scan using tbl_path_gist_idx on tbl_ltree  (cost=8.28..16.30 rows=1 width=68)

  Index Cond: (path @> $0)

  InitPlan 1 (returns $0)

    ->  Index Scan using tbl_path_gist_idx on tbl_ltree tbl_ltree_1  (cost=0.13..8.15 rows=1 width=32)

          Index Cond: (path ~ '*.res_1_1_1'::lquery)


-- working with plain text
SELECT t.*
FROM  (
   SELECT unnest(string_to_array(path, '.')) AS res_id
   FROM   tbl_txt
   WHERE  res_id = 'res_1_1_1'  -- your search term here
   ) t1
JOIN  tbl_txt t USING (res_id);


id
res_id
path


1
res_1
res_1

2
res_1_1
res_1.res_1_1

4
res_1_1_1
res_1.res_1_1.res_1_1_1
id	res_id	path
1	res_1	res_1
2	res_1_1	res_1.res_1_1
4	res_1_1_1	res_1.res_1_1.res_1_1_1