How to select from an ltree with format n.n.n.n and sort as numbers at each level?

Question

The default sort on the ltree column is as text. Example: I have 3 columns in my table id, parentid and wbs. The ltree column - wbs has 1.1.12, 1.1.1, 1.1.2 stored in different rows. The select query ordered by wbs column returns 1.1.1, 1.1.12, 1.1.2.

I need it to return 1.1.1, 1.1.2, 1.1.12

Answer 1

If all elements of ltree values are integers you can transform them to integer arrays, which may be used to sort:

with my_table(wbs) as (
values 
    ('1.1.12'::ltree),
    ('1.1.1'),
    ('1.1.2')
)

select wbs
from my_table
order by string_to_array(wbs::text, '.')::int[]

  wbs   
--------
 1.1.1
 1.1.2
 1.1.12
(3 rows)