Re: ORDER BY collation order

Joe wrote:
>
> I guess that means the encoding of the respective template0 database is
> what determines the sort order (UTF8 on FreeBSD and LATIN1 on Debian),
> right? Unfortunately, I'm unable to change the FreeBSD cluster since
> it's shared with others at our hosting provider. Is there some way to
> override the cluster setting, or plans to allow for database-specific
> collation orders?

The problem I see here is not just locale settings as Scott Marlowe
pointed out but also the fact that the server runs on FreeBSD with UTF8
encoding.

PostgreSQL relies on system (in this case libc) functions to handle
locale stuff and FreeBSD does not support UTF8. This led to FreeBSD
specific patch (which is in ports) that uses ICU library to handle UTF8
encoding. Sadly, it is not available for PostgreSQL 8.2 (original author
made patches only up to 8.1 and I ported it to 8.3 later). Also that
patch has to be enabled (which is not default iirc) when installing
PostgreSQL package.

So, since you say your cluster is hosted by some hosting company then
your only options are either to convince them to upgrade to 8.3
(provided the system locale is ok for your purposes) or wait until per
database collation is added (it was one of GSoC 08 projects and might
get into 8.4) and then convince them to upgrade.

--
Regards
Petr Jelinek (PJMODOS)