2

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

Kamal Panhwar
  • 2,345
  • 3
  • 21
  • 37
Santhosh
  • 23
  • 3

1 Answers1

2

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)
klin
  • 112,967
  • 15
  • 204
  • 232
  • Thank you. Does the LTREE provide any inbuilt feature to hold the sequence in which children of any node should be stored? – Santhosh Apr 27 '19 at 12:54
  • 1
    Ltree is based on *labels* which basically are just strings. It hasn't got any features to sort nodes on a path in another way than alphabetically. Maybe you should consider using integer arrays instead of ltree, of course in the case you don't use specialized ltree features like lqueries. – klin Apr 27 '19 at 13:17