Administration
Localization
16
Copyright
© Postgres Professional, 2016–2026
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Function of Localization
Locales and their Categories
Locale Providers
Server Configuration
Dates, Numbers and Currency Amounts
Client Configuration
Server and Client Messages
Collations
3
Function
Character encoding support
character encodings for different languages
character set conversion between server and client
Locale-dependent functionality
character sorting and comparison
upper, lower, and initcap functions
pattern matching
to_char functions for dates, numbers, and currency amounts
language for server and utility messages
PostgreSQL localization features allow storing text in various character
encodings. For example, for the French language all major character
encodings are supported, including UTF8, LATIN1 (ISO 8859-1), LATIN9
(ISO 8859-15), and WIN1252.
A client application can operate in an encoding different from the server’s
encoding. In such cases, character conversion between the client and server
is performed automatically.
Beyond supporting various encodings, localization affects the operation of
the following server functionalities:
●
Character sorting, such as in ORDER BY clauses or in comparison
operations (>, <)
●
Case conversion in the upper, lower, and initcap functions
●
Pattern matching in regular expressions and LIKE, SIMILAR TO
operators, including case-insensitive search
●
Formatting of dates, numbers, and currency amounts in the to_char
functions
●
Selecting the language for server and utility messages
4
Locales and their Categories
Locales
define the language, region, and character encoding,
for example, fr_FR.UTF8
installed in the operating system
Locale categories and their corresponding parameters
LC_COLLATE — string sort order
LC_CTYPE — character classification
LC_MESSAGES lc_messages language of messages
LC_MONETARY lc_monetary formatting of currency amounts
LC_NUMERIC lc_numeric formatting of numbers
LC_TIME lc_time formatting of dates and times
PostgreSQL relies on the localization capabilities provided by the operating
system. Therefore, the locales required for the DBMS should be pre-
configured in the OS.
Locales in the OS are typically specified in the “language_region.encoding”
format. For example, fr_CA.UTF8 represents the French language as
spoken in Canada (CA). Windows uses verbose locale names, such as
French_Canada.1252.
The language and region determine cultural preferences such as character
order, date formats, decimal separators, etc.
Sometimes it is necessary to combine the behavior of different locales. For
example, to use French date and time formats while keeping server
messages in English. PostgreSQL supports setting locale categories
separately via corresponding configuration parameters of the same name.
Starting with PostgreSQL 16, the unchangeable server parameters for the
LC_COLLATE and LC_CTYPE locale categories have been removed. To
obtain information about a database’s locale now, you should query the
pg_database system catalog table.
5
Locale Providers
Standard libc library
POSIX compliance
External ICU library
portable solution
extensive additional features
PostgreSQL supports various locale providers. Providers are libraries that
supply locale data and implement sorting. The standard libc provider uses
the system’s C library and provides its locales. These locales are used by
most operating system utilities.
PostgreSQL also supports the ICU provider, which uses the external ICU
library. ICU locale names are based on the IETF BCP 47 language tag
standard. These tags have formats “language-region” or simply “language”
(for example, fr-FR). When using ICU locales, PostgreSQL validates the
names and transforms them to their canonical form according to the
standard rules. The icu_validation_level parameter controls the message
level for locale validation checks.
Information about the selected provider and database locale is stored in the
pg_datlocprovider and daticulocale columns of the pg_database table.
For several reasons, libc settings are required in all databases (even those
using the ICU provider), so the datcollate and datctype columns always
contain information about the libc locale.
Commands and utilities for setting locale parameters also allow us to
explicitly select the provider. The libc provider is used by default.
6
Server Configuration
PostgreSQL
LC_COLLATE = en_US.UTF8
LC_CTYPE = en_US.UTF8
template0
initdb --locale-provider=icu --icu-locale='fr-FR'
CREATE DATABASE db
LOCALE_PROVIDER = 'libc'
TEMPLATE = template0
ENCODING = 'LATIN1'
LOCALE = 'fr_FR.ISO88591'
db
template1
localization settings
cannot be changed after
database creation
encoding
must match
the locale
When initializing the cluster, the provider and locale for template databases
are set using options of the initdb utility. By default, the system uses the libc
provider and the locale from the OS environment.
When a database is created, it inherits the localization settings of its
template. To create a database with different localization settings, we should
use the template0, as it is guaranteed not to contain any locale-specific data.
We can change the provider, character encoding, and the LC_CTYPE and
LC_COLLATE locale categories. Since the category values are typically the
same, it is convenient to set them using a single parameter (option):
LOCALE for libc and ICU_LOCALE for ICU.
The database encoding must be compatible with the LC_CTYPE and
LC_COLLATE locale parameters of that database. The C and POSIX
locales are compatible with any character set, while other libc provider
locales each have only one character set that will function correctly.
(An exception: on Windows, UTF-8 encoding can be used with any locale.)
ICU provider locales are compatible with most encodings.
8
Client Configuration
PostgreSQLpsql
terminal
chcp /
Terminal→Set Encoding
LC_ALL = fr_FR.UTF8
PGCLIENTENCODING = UTF8
backend
server_encoding
client_encoding
character conversion
(CREATE CONVERSION)
To configure localization for a client application, we need to do the following.
●
Check that the server settings are correct. As a minimum, ensure the
correct database encoding is used (the unchangeable server_encoding
parameter shows the encoding set when the database was created).
●
Check that the required locales are installed in the client OS and
configure the locale categories (the LC_* environment variables) in the
OS session.
●
Configure the application’s encoding and check the settings of the output
device.
For example, psql on Windows may use the Windows-1252 encoding, in
this case in the terminal (cmd.exe) run the chcp 1252 command and set a
true type font (e.g., Lucida Console).
●
After connecting to the database, check the client_encoding parameter.
This parameter controls character set conversion between the client and
server. If needed, set it to the application’s encoding. The client_encoding
value can also be set using the PGCLIENTENCODING environment
variable.
The backend automatically converts character set between the database
encoding (server_encoding) and the client encoding (client_encoding).
PostgreSQL comes preconfigured with the necessary character conversion
routines for most encoding pairs, which are stored in the pg_conversion
system catalog table. We can define an additional conversion routine using
CREATE CONVERSION, though this is rarely needed in practice.
10
Server Messages
psql
configure --enable-nls ...
PostgreSQL
backend
lc_messages
server log
ERREUR:
erreur de syntaxe
ERREUR:
erreur de syntaxe
PostgreSQL server (and utility) messages are translated into several
languages.
To display server messages in French, the PostgreSQL server must be
compiled with national language support enabled (configure --enable-nls).
Most packages are built with this option enabled.
The lc_messages configuration parameter controls the language of server
messages.
Server messages are not only sent to the client but also recorded to the
server log. When selecting a language other than English, make sure that
your server log analysis tools support that language. For example, pgBadger
works only with English messages.
More information on server message translation for translators
and developers: https://postgrespro.com/docs/postgresql/16/nls
11
Utility Messages
psql
configure --enable-nls ...
export LC_MESSAGES=fr
PostgreSQL
backend
=> SELECT version();
version
-------------------------
PostgreSQL 16.4
(1 ligne)
PostgreSQL utilities (psql, pg_dump, etc.) also support multiple languages.
To display utility messages in a language other than English, the
PostgreSQL client must be compiled with NLS support. The output language
is set on the client via the LC_MESSAGES environment variable (the server
parameter lc_messages affects only the server’s own messages, not the
client’s).
Most operating systems (including Windows) check environment variables
for message language in the following order: LANGUAGE, LC_ALL,
LC_MESSAGES, LANG.
13
Collations
Database objects
multiple collation rules within a single database
populated during database creation
Usage
cluster
database
table column
expression
Standard collations
default, C, POSIX
unicode, ucs_basic
PostgreSQL provides special objects called collations that enable different
sorting and comparison methods for textual data within a single database.
Collations allow using a sort order different from the database default.
They can be applied to table columns, domain definitions, and directly
in expressions that sort or compare text strings. When creating a new
collation, we specify the provider and character classification.
The initial set of collations is created during cluster initialization: collations
are loaded for all locales available in the OS. Later, when new locales are
added to the OS, we can load their collation rules into the database.
The standard C and POSIX collations behave identically and they are
created for all server encodings. Under these collations, only Latin
characters from A to Z are treated as letters, while all other characters are
sorted according to their code points in the given encoding. The collation
named default uses the LC_COLLATE, LC_CTYPE, and provider values
defined at database creation. Additionally, the unicode and ucs_basic
collations defined in the SQL standard are also available.
All available collations can be viewed in the pg_collation system catalog
table.
14
libc Provider
Character order
depends on the implementation in the OS
library version is tracked
Used by default
for the cluster and database
Collations in PostgreSQL that use the libc provider behave identically to
those in the OS. However, various OS may implement the libc library
differently. Consequently, sorting for the same locales may differ depending
on the database server’s OS. If an application needs to support database
operations across different operating systems (including logical replication),
make sure that collation works consistently across all platforms.
A more serious potential issue is installing a new version of the libc library in
the OS. For example, this can happen when migrating to a server with a
newer OS version. Changes to the collations used by the database in the
new libc version can lead to incorrect index operation and other issues.
15
ICU Provider
Character order
depends on the library version rather than the OS
library version is tracked
Additional features
control over the sorting order of character groups
custom collations
To use the ICU provider for collations, PostgreSQL must be built with ICU
library support. The ICU library and its collations work identically across all
operating systems.
The ICU library allows modifications to collations without changing the
language or region. We can specify different sorting orders for individual
character groups. For example, which character group should come first:
letters, digits or currency symbols; uppercase or lowercase letters. We can
configure these rules by including the corresponding parameters in the
language tag.
Additional collations are defined using a special syntax, with examples
provided in the demonstration.
17
Determinism
Deterministic collations
string equality is based on byte-for-byte match
Non-deterministic collations
strings consisting of different bytes can be considered equal
greater flexibility
lower performance
do not support LIKE and other operations
A collation can be either deterministic or non-deterministic, depending on the
type of comparison.
Deterministic comparisons consider strings equal only if they consist of
identical bytes. In non-deterministic comparisons, strings can be considered
equal even if their byte sequences differ. For example, in Unicode the
French letter é (e-acute) can be represented either as a single character,
Latin Small Letter E with Acute (U+00E9), or composed of the regular letter
“e” (U+0065) combined with the acute accent (U+0301).
All standard collations are deterministic. Custom collations are also
deterministic by default.
Non-deterministic collations offer greater flexibility: for instance, we can
define a unique constraint that ignores case or diacritical marks. However,
deterministic collations generally provide higher performance. Moreover,
non-deterministic collations do not support certain operations, such as
pattern matching.
19
Changes Tracking
Changing the provider library causes issues with:
sorting of index keys
string comparisons in integrity constraints
string comparisons in queries,
etc.
The library version number is stored in the system catalog
When the installed version changes, a warning is issued
libc does not guarantee version stability and cross-platform compatibility
Changes in the provider library that alter comparison behavior can affect the
collations used in the database and lead to the following problems:
●
Incorrect query execution due to broken ordering of index keys
●
Data corruption due to changed string comparison behavior in CHECK
constraints and triggers
●
Incorrect results due to changed string comparison behavior in queries,
procedural code, and row-level security policies
To prevent data corruption, PostgreSQL issues warnings when library
changes might affect existing collations.
When a collation is created, the current version number of the library is
stored in the system catalog, in the pg_collation and pg_database tables.
Each time the collation is used, the stored version number is compared with
the current one, and warnings are generated if they do not match.
If a mismatch is detected, you should rebuild any indexes that use the
changed collation, and also review all procedures, constraints, and queries
that use that collation.
It is important to remember that, unlike ICU, the libc provider does not
guarantee consistent collation behavior even when library versions match.
21
Takeaways
The necessary locales for the DBMS must be installed in the OS
before initializing a cluster
The client and server can operate using different character
encodings with automatic character conversion
Server and client messages are available in many languages
Collations rely on external libraries, and changes to these
libraries can result in data corruption and incorrect results
22
Practice
1. Transfer data between databases with different encodings.
Create a database using the LATIN1 character encoding.
Create a table and insert rows containing French accent
characters. Create a backup of the database using the pg_dump
utility. Restore the table from the backup into a database with
UTF8 encoding.
2. Get the number for today’s day of the week.
Does this number depend on localization settings?
1. To create a database with LATIN1 encoding:
●
Make sure the required locale is installed in the OS.
●
In the CREATE DATABASE command, use the template0 and the
ENCODING and LOCALE parameters.
2. Use the to_char function to get the day of the week number.
Valid date format patterns:
