PostgreSQL Collations

List Collations

Collations in PostgreSQL are available depending on operating system support. For example, in Ubuntu type the following to list the names of the available collations:

locale -a

The same locales are available in PostgreSQL in the pg_collation catalog (mappings from an SQL name to operating system locale categories).

select * from pg_collation;

collname   | collnamespace | collowner | collencoding | collcollate | collctype  
-----------+---------------+-----------+--------------+-------------+------------ 
default    |            11 |        10 |           -1 |             | 
C          |            11 |        10 |           -1 | C           | C 
POSIX      |            11 |        10 |           -1 | POSIX       | POSIX 
C.UTF-8    |            11 |        10 |            6 | C.UTF-8     | C.UTF-8 
en_AG      |            11 |        10 |            6 | en_AG       | en_AG 
en_AG.utf8 |            11 |        10 |            6 | en_AG.utf8  | en_AG.utf8 
en_AU      |            11 |        10 |            6 | en_AU.utf8  | en_AU.utf8 
en_AU.utf8 |            11 |        10 |            6 | en_AU.utf8  | en_AU.utf8 
en_BW      |            11 |        10 |            6 | en_BW.utf8  | en_BW.utf8 
en_BW.utf8 |            11 |        10 |            6 | en_BW.utf8  | en_BW.utf8 
en_CA      |            11 |        10 |            6 | en_CA.utf8  | en_CA.utf8 
en_CA.utf8 |            11 |        10 |            6 | en_CA.utf8  | en_CA.utf8 
en_DK      |            11 |        10 |            6 | en_DK.utf8  | en_DK.utf8 
en_DK.utf8 |            11 |        10 |            6 | en_DK.utf8  | en_DK.utf8 
en_GB      |            11 |        10 |            6 | en_GB.utf8  | en_GB.utf8 
en_GB.utf8 |            11 |        10 |            6 | en_GB.utf8  | en_GB.utf8 
en_HK      |            11 |        10 |            6 | en_HK.utf8  | en_HK.utf8 
en_HK.utf8 |            11 |        10 |            6 | en_HK.utf8  | en_HK.utf8 
en_IE      |            11 |        10 |            6 | en_IE.utf8  | en_IE.utf8 
en_IE.utf8 |            11 |        10 |            6 | en_IE.utf8  | en_IE.utf8 
en_IN      |            11 |        10 |            6 | en_IN       | en_IN 

Locale support is initialized when the database is created. After that, you can no longer change the locale, because the default collation selects LC_COLLATE and LC_CTYPE values are specified at database creation time. Appropriate language settings are defined in the below subcategories.

Set Collation for a Database

If you want to create a database with a locale different from the collation of the template database:

CREATE DATABASE polish_database  LC_COLLATE 'pl_PL.UTF-8' LC_CTYPE 'pl_PL.UTF-8';

you will receive an error:

ERROR:  new collation (pl_PL.UTF-8) is incompatible with the collation of the template database (en_US.UTF-8)
HINT:  Use the same collation as in the template database, or use template0 as template.

This happens because the new database is created as a clone of the standard system database template1, which may contain encoding-specific or locale-specific data, whereas template0 doesn’t.

CREATE DATABASE polish_database TEMPLATE template0 LC_COLLATE 'pl_PL.UTF-8' LC_CTYPE 'pl_PL.UTF-8';

Set Collation for a Table

It’s possible to set collation for each column using COLLATE clause. If the clause is omitted, the collation is the default for the new column type.

Create table collated_polish (some_text text collate "pl_PL.utf8");

When we don’t have the appropriate collation when doing initdb, but we do have the appropriate locale intalled on the system, we need to create a collation:

Create collation ru (locale = "ru_RU.utf8");

After that the collation will be added to the pg_collation table.

Set Collation for the Column:

CREATE TABLE dictionary (
   a text COLLATE "fr_FR",
   b text COLLATE "pl_PL",
   c text COLLATE "de_DE",
	 ...
   ...
);

Changing the Definition of Collation

PostgreSQL provides the ALTER COLLATION statement which can:

• change the name of collations

  ALTER COLLATION collation_name RENAME TO new_name

• change the owner of collations

  ALTER COLLATION collation_name OWNER TO new_owner

• change the schema of collations

  ALTER COLLATION collation_name SET SCHEMA new_schema

For example:

ALTER COLLATION “ru_RU” RENAME TO russian