Re: ORDER BY and Unicode

On Wed, 12 May 2004, M. Bastin wrote:

> >  > 2.  "ORDER BY mycolumn" gives a wrong sort order.
> >>
> >>  Uppercase ASCII characters come first, then
> >>  lowercase ASCII, then accented characters...
> >>  This really isn't what a human would like to see.
> >
> >This is driven by locale, what LC_COLLATE value
> >was the database created with (if you don't know then
> >pg_controldata should give that to you)?
> >
> >It sounds like the locale is "C" locale which means
> >sort by byte value or perhaps the locale is one that isn't
> >for the correct encoding.
>
> I've found this:
> <http://www.postgresql.org/docs/7.4/interactive/charset.html#LOCALE>
>
> "locale -a" isn't recognized on OS X.  How else can I find the
> possible locales?

Well, you can try looking around the system, maybe /usr/share/locale
or such would have the locale set.

> And how can I do an initdb so that sorting on Unicode will work for
> French, Greek, Japanase, etc. users of a single database?

AFAIK, you can't really at this time.  With an appropriately crafted
locale, you could probably get reasonably close, but I've never actually
tried to work with creating one so I don't know what's involved. And, if
two languages had different rules for two characters you'd not be
supporting both.