Forum OpenACS Development: ORDER BY ltree_column

Collapse
1: ORDER BY ltree_column
Posted by Michael Aram on 10/21/15 01:40 PM
Dear community,

we have in our system some legacy SQL statements that contain something like ... ORDER BY ltree2text(page_order)::integer ..., where the page_order column contains "ltree values" such as "123.456.789". We are currently seeing errors where PostgreSQL complains about not being able to cast these values to integer, which seems perfectly sensible. The strange thing is, that this code is around for many years; so I am wondering, if [a] we simply were lucky all the time (all values were integers) or if, for example, [b] the ltree2text function has changed in recent versions of PostgreSQL or something like that... Is my assumption that we were lucky and that the code should simply be changed to ... ORDER BY page_order ... correct?

Collapse
2: Re: ORDER BY ltree_column (response to 1)
Posted by Gustaf Neumann on 10/21/15 08:36 PM
probably just luck. there is no straightforward way to cast an ltree index in the general case to an integer, since the elements of an ltree path can be as well alphanumeric.