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
>
