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) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql