Search code examples
postgresqllocale

How to use PostgreSQL upper() function with a different locale?

I have a PostgreSQL database on a shared host and the result of using the upper function is different in my local database because of the locale setting.

Here is what I want, and have in my local environment:

SELECT version();
-- "PostgreSQL 8.4.16 on i386-apple-darwin10.8.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5666) (dot 3), 64-bit"

SHOW LC_COLLATE;
-- "fr_FR.UTF-8"

SELECT upper('étienne');
-- "ÉTIENNE"

Here is what I have in the production environment:

SELECT version();
-- "PostgreSQL 9.0.13 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 64-bit"

SHOW LC_COLLATE;
-- "C"

SELECT upper('étienne');
-- éTIENNE

Now, because the production environment is in a shared host, I cannot change the locale due to the host policies. So, is there another way to have the expected result when using upper function?

Solution

  • Note that you cannot override the collation (like demonstrated by other answers) in your versions 8.4 or 9.0. This feature was introduced with Postgres 9.1.

    In earlier versions, you cannot change the collation chosen when the database was created. The COLLATE key word does not exist and you get an error if you try SET LC_COLLATE = ...:

    ERROR: parameter "lc_collate" cannot be changed

    You need a more current version to do that.

    -> SQLfiddle

    Sorry, but you are out of luck.