On 23rd of September, Heikki Linnakangas committed patch that was written by Radek Strnad (actually committed patch is a stripped-down version of original).

What it does? It adds ability to have (finally!) different collation order and character categories for different databases.

Up until now, you had to set LC_COLLATE and LC_CTYPE when doing initdb, and then it was unchangeable. Database initdb'ed as LATIN2 would not work fully correctly when dealing with UTF-8.

Now, it has been changed:

Make LC_COLLATE and LC_CTYPE database-level settings. Collation and
ctype are now more like encoding, stored in new datcollate and datctype
columns in pg_database.
 
This is a stripped-down version of Radek Strnad's patch, with further
changes by me.

What it gives us?

For example, let's assume we have PostgreSQL instance initialized with “C" locale:

# \l
                                List of databases
   Name    | Owner  | Encoding  | Collation | Ctype |     Access Privileges
-----------+--------+-----------+-----------+-------+----------------------------
 depesz    | depesz | SQL_ASCII | C         | C     |
 postgres  | pgdba  | SQL_ASCII | C         | C     |
 template0 | pgdba  | SQL_ASCII | C         | C     | {=c/pgdba,pgdba=CTc/pgdba}
 template1 | pgdba  | SQL_ASCII | C         | C     | {=c/pgdba,pgdba=CTc/pgdba}
(4 rows)

Since “C" doesn't know anything about polish national characters it will not be able to sort polish text correctly. Nor can it upper() correctly:

# SET client_encoding = 'UTF-8';
SET
 
# SELECT c, UPPER(c) FROM (VALUES ('a'), ('ć'), ('e'), ('ź'), ('x'), ('ł'), ('ś'), ('w')) AS x (c) ORDER BY c;
 c | UPPER
---+-------
 a | A
 e | E
 w | W
 x | X
 ć | ć
 ł | ł
 ś | ś
 ź | ź
(8 ROWS)

(If you're not familiar with polish alphabet, just trust me – it's not ok. I'll show how it should look in a moment.).

With Pg <= 8.3 I would have to reinitdb, and thus convert all databases to new collation. Which is a bit troublesome.

Luckily, with 8.4, I can add new database, that will use another collation:

# CREATE DATABASE depesz_pl WITH encoding 'utf8' COLLATE 'pl_PL.UTF-8' ctype 'pl_PL.UTF-8' template template0;
CREATE DATABASE

The only problem is that I need to use template0. Otherwise I'll get:

# CREATE DATABASE depesz_pl WITH encoding 'utf8' COLLATE 'pl_PL.UTF-8' ctype 'pl_PL.UTF-8';
ERROR:  NEW collation IS incompatible WITH the collation OF the template DATABASE (C)
HINT:  USE the same collation AS IN the template DATABASE, OR USE template0 AS template

(of course I could have created template1_pl, but then – I would have to create this template somehow 🙂

So, having this new database, let's try to run our test query in it:

# \c depesz_pl
You are now connected TO DATABASE "depesz_pl".
 
# SHOW client_encoding ;
 client_encoding
-----------------
 UTF8
(1 ROW)
 
# SELECT c, UPPER(c) FROM (VALUES ('a'), ('ć'), ('e'), ('ź'), ('x'), ('ł'), ('ś'), ('w')) AS x (c) ORDER BY c;
 c | UPPER
---+-------
 a | A
 ć | Ć
 e | E
 ł | Ł
 ś | Ś
 w | W
 x | X
 ź | Ź
(8 ROWS)

YES! It works!

Also, \l output has been also modified to accommodate new information:

# \l
                                    List of databases
   Name    | Owner  | Encoding  |  Collation  |    Ctype    |     Access Privileges
-----------+--------+-----------+-------------+-------------+----------------------------
 depesz    | depesz | SQL_ASCII | C           | C           |
 depesz_pl | depesz | UTF8      | pl_PL.UTF-8 | pl_PL.UTF-8 |
 postgres  | pgdba  | SQL_ASCII | C           | C           |
 template0 | pgdba  | SQL_ASCII | C           | C           | {=c/pgdba,pgdba=CTc/pgdba}
 template1 | pgdba  | SQL_ASCII | C           | C           | {=c/pgdba,pgdba=CTc/pgdba}
(5 rows)

One thing – you can't change collation/ctype of existing database. Reason for this is pretty simple: indexes rely on collation (and can rely on ctype). So changing collation/ctype would require reindexation of all data. While this would be technically possible – you can do it by simply dumping database, creating new database with desired locale, and loading dump.

Of course this is still a long way to make PostgreSQL fully functional in multi-language environments, but at the very least it is a step in right direction. Awaited, and important step.