Re: LTREE extension and "order by"

From: Ivan Polak <ivan(dot)polak(at)f4s(dot)sk>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: LTREE extension and "order by"
Date: 2011-07-21 17:31:21
Message-ID: CAN=kwkvHa511JyEaFzfh5J88dXECkSXUBnrXqZ6t9C+VyE+aPA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)hotmail(dot)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(dot)polak(at)f4s(dot)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(at)hotmail(dot)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(dot)polak(at)f4s(dot)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(dot)p(at)gmail(dot)com>:
>> >> > Hi.
>> >> >
>> >> > You should convert path to integer[].
>> >> >
>> >> > 2011/7/20, Ivan Polak <ivan(dot)polak(at)f4s(dot)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(at)postgresql(dot)org)
>> >> >> To make changes to your subscription:
>> >> >> http://www.postgresql.org/mailpref/pgsql-sql
>> >> >>
>> >> >
>> >> >
>> >> > --
>> >> > ------------
>> >> > pasman
>> >> >
>> >> > --
>> >> > Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>> >> > To make changes to your subscription:
>> >> > http://www.postgresql.org/mailpref/pgsql-sql
>> >> >
>> >>
>> >> --
>> >> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-sql
>> >>
>> >
>> >
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
>

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Ozer, Pam 2011-07-21 23:09:28 Need to return one field for multiple Rows
Previous Message Pavel Stehule 2011-07-21 16:48:00 Re: LTREE extension and "order by"