PostgreSQL Collations

Patrycja Dybka, Community Manager at Vertabelo

by
Patrycja Dybka
Community Manager at Vertabelo

Posted: January 5, 2014

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.

LC_COLLATE String sort order
LC_CTYPE Character classification (What is a letter? Its upper-case equivalent?)
LC_MESSAGES Language of messages
LC_MONETARY Formatting of currency amounts
LC_NUMERIC Formatting of numbers
LC_TIME Formatting of dates and times

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

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! Learn the basics of T-SQL for SQL Server, retrieve data from an SQL Server database and build simple reports. View course Discover our other courses: