[SOLVED] Only retrieve records with a single ancestor node of a certain type

Only retrieve records with a single ancestor node of a certain type

I have a postgres database. In it is a table called structure.

In the structure table is an ltree field called path. It holds data like the following

f55219.f55468.f55635.f44939.p10900.f45675.p10902
f55219.f55468.f43355.f55340.p12120
f55219.f55468.f55635.f44939.p11153.f46590.p11222
f55219.f55468.f43355.f48299.p11620

I'm trying to work out how I query the structure table to only return records with a single 'p*' value in the ltree. In the data I've pasted above, that'd be the 2nd and 4th rows.

In case it matters, there can be any number (0 - infinity) of f* records between the two p* records in the data.

Thanks for the help!

Solution

Something like that should work:

with structure(path) as (values
('f55219.f55468.f55635.f44939.p10900.f45675.p10902'::ltree),
('f55219.f55468.f43355.f55340.p12120'),
('f55219.f55468.f55635.f44939.p11153.f46590.p11222'),
('f55219.f55468.f43355.f48299.p11620')
)

select * from structure where path ~ '*.p*.*' and not (path ~ '*.p*.*.p*.*');
                path
------------------------------------
 f55219.f55468.f43355.f55340.p12120
 f55219.f55468.f43355.f48299.p11620
(2 rows)

First condition searches paths that has at least one label which starts with p letter. Second - searches for paths that doesn't have 2 labels which starts with 'p'.