I wanted to fetch Postgres ltree hierarchy after certain level using subltree and column name. I am able to fetch when i provide the exact input but not able to do so with column name.
This is working fine:
select *
from audit.EMPLOYEE_TOTALS_BY_DAY_WITHOUT_FP_MV_V4
where ecd_path ~ '10130882.11000114.10152749.10148495.10125148.*{1,}'
and customer_id = 2955
limit 10;
But this is not. Please correct me with right query
select *
from audit.EMPLOYEE_TOTALS_BY_DAY_WITHOUT_FP_MV_V4
where ecd_path ~ subltree(ecd_path, 0, nlevel(ecd_path)-1)'.*{1,}'
and customer_id = 2955
limit 10;
;
I am very new to Postgres Ltree . Correct me if anything is wrong here
If your goal is to return everything whose path is more than 5 labels deep, then it would seem that this could be accomplished by doing: