sqldatabasepostgresqlltree

POSTGRES : ltree ancestors is not ordered

I am looking for information but I can't find it in the documentation I execute a request on my path to ask for the ancestors, I receive all the ancestors but they are all mixed, do you have an idea of my error?

Is it possible to have the order represented directly in the path with ltree?

Thanks in advance

select id from magic where path @> '1.38.39.41.42.43.49'

Result : 
49
1
39
38
42
41
43

However I would like the following result

select id from magic where path @> '1.38.39.41.42.43.49'

Result :
49 
43
42
41
39
38
1
Solution

  • Ok I found this which is good for me however I don't know if there is anything native to ltree for the order

    select id from res_partner where path @> '1.38.39.41.42.43.49' order by string_to_array(path::text, '.')::int[] desc