Hmm, I'm using PostgreSQL 8.4 and it worked. Try to use the function ltree2text instead of ::text. select * from comments where article_id = 2 order by cast(string_to_array(ltree2text(path),'.') as integer[]);
2011/7/21 Ivan Polak <ivan.po...@f4s.sk> > hi, thank you, but there is error: > > ERROR: cannot cast type ltree to text > LINE 1: ... article_id = 2 order by string_to_array(path::text,'.'):... > > Ivan > > On 21 July 2011 17:25, Carla <cgourof...@hotmail.com> wrote: > > Try it: > > select * from comments where article_id = 2 order by > > string_to_array(path::text,'.')::integer[]; > > > > 2011/7/21 Ivan Polak <ivan.po...@f4s.sk> > >> > >> Hi, thank you for your answer, please can You send me complete select > >> command how to convert ltree column to integer[] and use it to order > >> by. > >> > >> thanks > >> > >> Ivan > >> > >> 2011/7/21 pasman pasmański <pasma...@gmail.com>: > >> > Hi. > >> > > >> > You should convert path to integer[]. > >> > > >> > 2011/7/20, Ivan Polak <ivan.po...@f4s.sk>: > >> >> Hi, > >> >> > >> >> in postgreSQL (with LTREE extension) database I have the following > >> >> table "comments": > >> >> > >> >> id BIGINT /* id */ > >> >> article_id BIGINT /*article-id */ > >> >> parent_id BIGINT > >> >> comment TEXT > >> >> path LTREE > >> >> level INTEGER /* level */ > >> >> > >> >> with the following rows: > >> >> > >> >> id article_id comment parent_id path level > >> >> 1 1 aaaa 1 1 > >> >> 2 1 bbbb 1 1.2 2 > >> >> 3 1 cccc 2 1.2.3 3 > >> >> 4 1 dddd 2 1.4 2 > >> >> 5 1 eeee 4 1.4.5 3 > >> >> 6 1 ffff 6 1 > >> >> 7 1 gggg 6 6.7 2 > >> >> 8 1 hhhh 6 6.8 2 > >> >> 9 1 iiii 9 1 > >> >> 10 1 jjjj 10 1 > >> >> 11 1 kkkk 5 1.4.5.11 4 > >> >> > >> >> and I need to select complete tree (with correct order of comments). > >> >> > >> >> SELECT * from comments where article_id = 2 order by <???> > >> >> > >> >> when I used: > >> >> > >> >> SELECT * from comments where article_id = 2 order by path > >> >> > >> >> the result is: > >> >> > >> >> id comment path > >> >> 1 aaaa 1 > >> >> 2 bbbb 1.2 > >> >> 3 cccc 1.2.3 > >> >> 4 dddd 1.4 > >> >> 5 eeee 1.4.5 > >> >> 11 kkkk 1.4.5.11 > >> >> 10 jjjj 10 > >> >> 6 ffff 6 > >> >> 7 gggg 6.7 > >> >> 8 hhhh 6.8 > >> >> 9 iiii 9 > >> >> > >> >> BUT, it is wrong, because comment with id = 10 is after comment with > >> >> id=11 > >> >> > >> >> (i know, this is correct, because ordering by column PATH [as TEXT], > >> >> and 10 is 'after' 1.4.5.11) > >> >> > >> >> , but I need : > >> >> > >> >> id comment path > >> >> 1 aaaa 1 > >> >> 2 bbbb 1.2 > >> >> 3 cccc 1.2.3 > >> >> 4 dddd 1.4 > >> >> 5 eeee 1.4.5 > >> >> 11 kkkk 1.4.5.11 > >> >> 6 ffff 6 > >> >> 7 gggg 6.7 > >> >> 8 hhhh 6.8 > >> >> 9 iiii 9 > >> >> 10 jjjj 10 > >> >> > >> >> thanks > >> >> > >> >> Ivan > >> >> > >> >> -- > >> >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > >> >> To make changes to your subscription: > >> >> http://www.postgresql.org/mailpref/pgsql-sql > >> >> > >> > > >> > > >> > -- > >> > ------------ > >> > pasman > >> > > >> > -- > >> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > >> > To make changes to your subscription: > >> > http://www.postgresql.org/mailpref/pgsql-sql > >> > > >> > >> -- > >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-sql > >> > > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > >
