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

Question

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?

Answer 1

For 9.1 and up, you can just choose collation locally for the query.
Sadly, considering your version numbers, that would require an upgrade, so may or may not be helpful.

SELECT UPPER('étienne' COLLATE "C")      C_Collation,
       UPPER('étienne' COLLATE "fr_FR") FR_Collation;

C_Collation        FR_Collation
--------------------------------------
éTIENNE            ÉTIENNE

An SQLfiddle to test with.

Answer 2

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.