Postgres Pro
Downloads
Home   >   mailing lists

Thread: problem with ORDER BY

problem with ORDER BY

From
Nicolas KOWALSKI
Date:
Hello,

I do not understand why the following ORDER BY statment does not work
as I would expect:

1) I defined a simple table with only one column, containing urls:

mon=> \d url
    Table "public.url"
 Column | Type | Modifiers
--------+------+-----------
 url    | text | not null
Indexes:
    "url_pkey" primary key, btree (url)


2) I populated it, some urls starting with https, others with http

3) When I want to sort them, I get this "strange" ordering:

mon=> SELECT * FROM url ORDER BY url asc ;
              url
-------------------------------
 http://imag.fr/
 https://gmail.com/
 https://mail.google.com/mail/
 https://www.sixxs.net/
 http://www.google.com/
 http://www.google.com/reader/
 http://www.google.fr/
 http://www.postgresql.org/
(8 rows)


Should'nt I get these values in the following order ?

 http://imag.fr/
 http://www.google.com/
 http://www.google.com/reader/
 http://www.google.fr/
 http://www.postgresql.org/
 https://gmail.com/
 https://mail.google.com/mail/
 https://www.sixxs.net/


Thanks,
--
Nicolas

Re: problem with ORDER BY

From
Martijn van Oosterhout
Date:
On Fri, Feb 01, 2008 at 11:06:07AM +0100, Nicolas KOWALSKI wrote:
> Hello,
>
> I do not understand why the following ORDER BY statment does not work
> as I would expect:
>
> 3) When I want to sort them, I get this "strange" ordering:

Show us your collation order:
show lc_collate;

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy
Attachment

Re: problem with ORDER BY

From
Nicolas KOWALSKI
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:

> On Fri, Feb 01, 2008 at 11:06:07AM +0100, Nicolas KOWALSKI wrote:
>>
>> I do not understand why the following ORDER BY statment does not work
>> as I would expect:
>>
>> 3) When I want to sort them, I get this "strange" ordering:
>
> Show us your collation order:
> show lc_collate;

Here it is:

mon=> show lc_collate;
 lc_collate
-------------
 en_US.UTF-8
(1 row)

Thanks,
--
Nicolas

Re: problem with ORDER BY

From
Tom Lane
Date:
Nicolas KOWALSKI <niko@petole.dyndns.org> writes:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>> Show us your collation order:
>> show lc_collate;

> Here it is:

> mon=> show lc_collate;
>  lc_collate
> -------------
>  en_US.UTF-8
> (1 row)

The ordering you showed is correct according to en_US (and most other
non-C locales).  The ordering you want is used in "C" locale.
Unfortunately you have to re-initdb to change the locale of a
database :-(
    initdb --locale=C

            regards, tom lane

Re: problem with ORDER BY

From
Nicolas KOWALSKI
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:

> The ordering you showed is correct according to en_US (and most other
> non-C locales).  The ordering you want is used in "C" locale.
> Unfortunately you have to re-initdb to change the locale of a
> database :-(
>     initdb --locale=C

Thanks Tom, using the C locale as indicated gets this right in our
database.

Best regards,
--
Nicolas

Re: problem with ORDER BY

From
"Vyacheslav Kalinin"
Date:
I am guessing that collation rules are not applied to bytea columns, so one can compare text according to C locale anyway if he populates bytea column or just does something like

select * from url order by byteain(textout(url))

Re: problem with ORDER BY

From
Nicolas KOWALSKI
Date:
"Vyacheslav Kalinin" <vka@mgcp.com> writes:

> I am guessing that collation rules are not applied to bytea columns, so one
> can compare text according to C locale anyway if he populates bytea column
> or just does something like
>
> select * from url order by byteain(textout(url))

Thanks for your suggestion; it works well :-)

mon=> select * from url order by byteain(textout(url)) ;
              url
-------------------------------
 http://imag.fr/
 http://www.google.com/
 http://www.google.com/reader/
 http://www.google.fr/
 http://www.postgresql.org/
 https://gmail.com/
 https://mail.google.com/mail/
 https://www.sixxs.net/
(8 rows)


Best regards,
--
Nicolas