Fetch the unique rows from postgres database as per longest unique path in ltree

Question

I am working over postgres in db we have below table where we have PATH column as ltree for storing hierarchy ids.

Table (Location)-

| ID(PK) |   User   |PATH (parent hierarchy) |
|--------|----------|---------|
|    1   |  Parent  |   null  |
|    2   |  Child-1 |   1     |
|    3   |  Child-2 |   1.2   |
|    4   |  Child-3 |   1.2.3 |
|    5   |  AParent |   null  |
|    6   |  AChild-2|   5     |
|    7   |  AChild-3|   5.6   |
|    8   |  AChild-4|   5.6   |

tried below query -

select loc.user,STRING_TO_ARRAY(concat(loc.PATH::text,'.',loc.id),'.') as path
from location loc
WHERE LOC.NAME ilike '%child%';

Result -

|   ID   |   User   |   PATH  |
|--------|----------|---------|
|    2   |  Child-1 |{1,2}    |
|    3   |  Child-2 |{1,2,3}  |
|    4   |  Child-3 |{1,2,3,4}|
|    6   |  AChild-2|{5,6}    |
|    7   |  AChild-3|{5,6,7}  |
|    8   |  AChild-4|{5,6,8}  |

Here you can see we have multiple hierarchies in the table but i want the unique hierarchies till the leaf e.g.

Hierarchy -1
A>B>C
Hierarchy -2
A>B>C>D`

expected result should be -

   A>B>C>D

cause A>B>C also comes under same umbrella. so the final table expected result -

|   ID   |   User   |   PATH   |
|--------|----------|----------|
|    4   |  Child-3 |{1,2,3,4} |
|    7   |  AChild-3|{5,6,7}   |
|    8   |  Child-4 |{5,6,8}   |

Same table managing the parent-child relationship as path showing the hierarchy of user.

Please let me know if anything else required.

Answer 1

You can find leaves by examining whether a node has any descendants:

select *
from location l1
where l1.path is not null
and not exists (
    select from location l2
    where l2.path <@ l1.path 
    and l2.path <> l1.path
    )
order by l1.id

Test it in db<>fiddle.

The query is more complex than it could be because your use of ltree is somewhat unlogical. Roots should not be null, all the hierarchy information should be included in the ltree column, while you unnecessary mixed it with id.