Thread: LTREE extension and "order by"
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
Hi. You should convert path to integer[]. 2011/7/20, Ivan Polak <ivan.polak@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
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 <pasman.p@gmail.com>: > Hi. > > You should convert path to integer[]. > > 2011/7/20, Ivan Polak <ivan.polak@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 >
Try it:
select * from comments where article_id = 2 order by string_to_array(path::text,'.')::integer[];
select * from comments where article_id = 2 order by string_to_array(path::text,'.')::integer[];
2011/7/21 Ivan Polak <ivan.polak@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 <pasman.p@gmail.com>:--> Hi.
>
> You should convert path to integer[].
>
> 2011/7/20, Ivan Polak <ivan.polak@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
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 <cgourofino@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.polak@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 <pasman.p@gmail.com>: >> > Hi. >> > >> > You should convert path to integer[]. >> > >> > 2011/7/20, Ivan Polak <ivan.polak@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 >> > >
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[]);
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.polak@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 <cgourofino@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.polak@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 <pasman.p@gmail.com>:
>> > Hi.
>> >
>> > You should convert path to integer[].
>> >
>> > 2011/7/20, Ivan Polak <ivan.polak@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
2011/7/21 Carla <cgourofino@hotmail.com>: > 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[]); > this does not work in older versions you can use http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Cast_to_varchar Regards Pavel Stehule
HI, thanks, thanks Carla, your solution is OK :-) (i`m using PostgreSQL 8.2, so Pavel is right). Ivan On 21 July 2011 18:28, Carla <cgourofino@hotmail.com> wrote: > 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.polak@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 <cgourofino@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.polak@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 <pasman.p@gmail.com>: >> >> > Hi. >> >> > >> >> > You should convert path to integer[]. >> >> > >> >> > 2011/7/20, Ivan Polak <ivan.polak@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 >> > >