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
