problem with ORDER BY
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
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/
Show quoted text
Those who make peaceful revolution impossible will make violent revolution inevitable.
-- John F Kennedy
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
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
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
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))
"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 likeselect * 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