Обсуждение: 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/ > 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))
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 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