16 Lesson 16: Administration / Localization

Page 1: Administration / Localization

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.

Page 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

Page 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

Speaker notes

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

https://postgrespro.com/docs/postgresql/current/multibyte#MULTIBYTE-CH ARSET-SUPPORTED

https://postgrespro.com/docs/postgresql/16/locale

Page 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

Speaker notes

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.

Page 5: Locale Providers

Standard libc library

• POSIX compliance
• External ICU library
  • portable solution
  • extensive additional features

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/locale#ICU-LANGUAGE-TAG

https://www.rfc-editor.org/rfc/bcp/bcp47.txt

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.

https://postgrespro.com/docs/postgresql/16/locale#LOCALE-PROVIDERS

Page 6: Server Configuration

{#source-figure-006 caption=“Source slide page 6: Server Configuration”}

Speaker notes

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.

Page 7: Locales and Categories

The server OS already has locales installed with the French language support:

student$ locale -a | grep fr

french
fr_FR
fr_FR.iso88591
fr_FR.utf8

The localization parameters for the OS session are controlled by environment variables:

student$ locale

LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

In addition to variables corresponding to locale categories, there are special variables: LC_ALL — when set, overrides all locale categories, even if they have been explicitly specified LANG — used for any locale categories that have not been explicitly set LANGUAGE — when set, takes precedence over LC_MESSAGES (explained in detail later) When PostgreSQL was installed from the package in the course VM, the main cluster was initialized using the OS session parameters and the libc provider. To set different values, you can use the –locale-provider, –locale (equivalent to LANG), and options for specific categories, for example:

student$ sudo pg_createcluster 16 new_cluster -- \
--locale-provider=icu \
--locale=en_US.UTF-8

PostgreSQL has four parameters that correspond to locale categories of the same name. Notice that there are fewer of them than in the OS:

=> SELECT name, setting, context, sourcefile
FROM pg_settings
WHERE name LIKE 'lc%';

    name     |   setting   |  context  |               sourcefile
-------------+-------------+-----------+-----------------------------------------
 lc_messages | en_US.UTF-8 | superuser | /etc/postgresql/16/main/postgresql.conf
 lc_monetary | en_US.UTF-8 | user      | /etc/postgresql/16/main/postgresql.conf
 lc_numeric  | en_US.UTF-8 | user      | /etc/postgresql/16/main/postgresql.conf
 lc_time     | en_US.UTF-8 | user      | /etc/postgresql/16/main/postgresql.conf
(4 rows)

These parameter values are written to the configuration file. Any user can modify the lc_monetary, lc_numeric, and lc_time parameters (context=user), while lc_messages can be changed only by a superuser. There are no corresponding server parameters for the LC_COLLATE and LC_CTYPE locale categories. When a backend starts, it resets the LC_ALL value and sets the OS environment variables for locale categories according to the lc_* configuration parameters. When a parameter is changed during a session, the backend sets the corresponding environment variable to match. The standard postgres, template0, and template1 databases use the LC_COLLATE and LC_CTYPE values, as well as the locale provider, that were set during cluster initialization. New databases by default copy the parameters of the template they use:

=> CREATE DATABASE admin_localization_utf8;

CREATE DATABASE

Page 8: Page 8

=> \x \list admin_localization_utf8|template1 \x

Expanded display is on.
List of databases
-[ RECORD 1 ]-----+------------------------
Name              | admin_localization_utf8
Owner             | student
Encoding          | UTF8
Locale Provider   | libc
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
ICU Locale        |
ICU Rules         |
Access privileges |
-[ RECORD 2 ]-----+------------------------
Name              | template1
Owner             | postgres
Encoding          | UTF8
Locale Provider   | libc
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
ICU Locale        |
ICU Rules         |
Access privileges | =c/postgres            +
                  | postgres=CTc/postgres

Expanded display is off.

Dates and LC\_TIME

Configuration parameter for the corresponding locale category:

=> SHOW lc_time;

   lc_time
-------------
 en_US.UTF-8
(1 row)

To use English month and day names in the to_char function’s format mask, add the TM prefix:

=> SELECT to_char(current_date, 'TMDay, DD TMMonth YYYY');

        to_char
------------------------
 Thursday, 11 June 2026
(1 row)

The same for French:

=> SET lc_time = 'fr_FR.UTF8';

SET

=> SELECT to_char(current_date, 'TMDay, DD TMMonth YYYY');

       to_char
---------------------
 Jeudi, 11 Juin 2026
(1 row)

Reset the parameter to its original value:

=> RESET lc_time;

RESET

The to_date and to_char functions let you explicitly specify the date and time format for input/output. The datestyle parameter affects implicit conversion between dates and text. Here are a few examples:

=> SET datestyle = 'Postgres'; SELECT current_setting('datestyle') AS datestyle, now();

SET
   datestyle   |                 now
---------------+-------------------------------------
 Postgres, MDY | Thu Jun 11 17:35:59.079869 2026 MSK
(1 row)

Page 9: Page 9

=> SET datestyle = 'SQL, DMY'; SELECT current_setting('datestyle') AS datestyle, now();

SET
 datestyle |              now
-----------+--------------------------------
 SQL, DMY  | 11/06/2026 17:35:59.108653 MSK
(1 row)

=> SET datestyle = 'SQL, MDY'; SELECT current_setting('datestyle') AS datestyle, now();

SET
 datestyle |              now
-----------+--------------------------------
 SQL, MDY  | 06/11/2026 17:35:59.137287 MSK
(1 row)

=> RESET datestyle; SELECT current_setting('datestyle') AS datestyle, now();

RESET
 datestyle |              now
-----------+-------------------------------
 ISO, MDY  | 2026-06-11 17:35:59.165596+03
(1 row)

Numbers and LC\_NUMERIC

Configuration parameter for the corresponding locale category:

=> SET lc_numeric = 'fr_FR.UTF8';

SET

The following digit group separators (G) and decimal separators (D) are used in France:

=> SELECT to_char('12345'::numeric, '999G999D00');

   to_char
-------------
   12 345,00
(1 row)

The same for the United States:

=> SET lc_numeric = 'en_US.UTF8';

SET

=> SELECT to_char('12345'::numeric, '999G999D00');

   to_char
-------------
   12,345.00
(1 row)

Currency Amounts and LC\_MONETARY

Configuration parameter for the corresponding locale category:

=> SET lc_monetary = 'fr_FR.UTF8';

SET

The money data type adds a currency code to the amount:

=> SELECT '12345'::money;

    money
-------------
 12 345,00 €
(1 row)

However, note that a table with this type can store only one currency, which will change along with the LC_MONETARY:

=> SET lc_monetary = 'en_US.UTF8';

SET

Page 10: Page 10

=> SELECT '12345'::money;

   money
------------
 $12,345.00
(1 row)

Page 11: Client Configuration

{#source-figure-011 caption=“Source slide page 11: Client Configuration”}

Speaker notes

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.

Page 12: Client and Server Working with Different Encodings

Create a database with LATIN1 encoding, while the client continues to use UTF8. Specify ICU as the locale provider. To create the database successfully, we need to use the template0: it is an empty database where indexes are guaranteed not to be corrupted by the encoding change:

=> CREATE DATABASE admin_localization_latin1
  LOCALE_PROVIDER = 'icu'
  ENCODING = 'LATIN1'
  ICU_LOCALE = 'fr-FR' LC_CTYPE='fr_FR.ISO88591' LC_COLLATE='fr_FR.ISO88591'
  TEMPLATE = template0;

CREATE DATABASE

In our case (creating a database with the ICU provider), we needed to specify the LC_CTYPE and LC_COLLATE parameters to match the chosen encoding.

=> \x \l admin_localization_latin1 \x

Expanded display is on.
List of databases
-[ RECORD 1 ]-----+--------------------------
Name              | admin_localization_latin1
Owner             | student
Encoding          | LATIN1
Locale Provider   | icu
Collate           | fr_FR.ISO88591
Ctype             | fr_FR.ISO88591
ICU Locale        | fr-FR
ICU Rules         |
Access privileges |

Expanded display is off.

Connect to admin_localization_latin1 and check that character conversion is working properly.

=> \c admin_localization_latin1

You are now connected to database "admin_localization_latin1" as user "student".

Client and server encodings:

=> SELECT name, setting, context
FROM pg_settings
WHERE name LIKE '%encoding';

      name       | setting | context
-----------------+---------+----------
 client_encoding | LATIN1  | user
 server_encoding | LATIN1  | internal
(2 rows)

The client (psql) sets the value of the client_encoding parameter. In interactive mode, the value is set based on the client’s encoding (the LC_CTYPE variable in the OS). But when running from a script, as in this demo, it is set to the database encoding. Let’s explicitly set the value to match the client locale:

=> \! locale | grep LC_CTYPE

LC_CTYPE="en_US.UTF-8"

=> SET client_encoding = 'UTF-8';

SET

Now characters will be correctly converted from UTF8 to LATIN1 and back:

=> CREATE TABLE tab AS SELECT 'Oh là là'::text AS col;

SELECT 1

=> SELECT * FROM tab;

   col
----------
 Oh là là
(1 row)

Page 13: Page 13

List of available encoding conversions for LATIN1:

=> \dcS iso_8859_1_to_*|*_to_iso_8859_1

                            List of conversions
   Schema   |        Name        |    Source     |  Destination  | Default?
------------+--------------------+---------------+---------------+----------
 pg_catalog | iso_8859_1_to_mic  | LATIN1        | MULE_INTERNAL | yes
 pg_catalog | iso_8859_1_to_utf8 | LATIN1        | UTF8          | yes
 pg_catalog | mic_to_iso_8859_1  | MULE_INTERNAL | LATIN1        | yes
 pg_catalog | utf8_to_iso_8859_1 | UTF8          | LATIN1        | yes
(4 rows)

=> \c admin_localization_utf8

You are now connected to database "admin_localization_utf8" as user "student".

Page 14: Server Messages

{#source-figure-014 caption=“Source slide page 14: Server Messages”}

Speaker notes

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

Page 15: Utility Messages

{#source-figure-015 caption=“Source slide page 15: Utility Messages”}

Speaker notes

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.

Page 16: Server and Client Messages

Server messages can be displayed in different languages. First, make sure that PostgreSQL was built with NLS support:

=> SELECT unnest(regexp_match(setting,'--enable-nls')) config
FROM pg_config()
WHERE name = 'CONFIGURE';

    config
--------------
 --enable-nls
(1 row)

List of available languages and their message files can be found here:

=> SELECT setting
FROM pg_config()
WHERE name = 'LOCALEDIR';

      setting
-------------------
 /usr/share/locale
(1 row)

student$ ls -C /usr/share/locale

aa
     de
  fur
    ku_IQ
  oc
    ta
ab
     de_AT
  fy
    kv
  om
    ta_LK
ace
     de_CH
  ga
    kw
  or
    tcy
ach
     doi
  gd
    ky
  os
    te
af
     dsb
  gez
    la
  pa
    tg
ak
     dv
  gl
    lb
  pam
    th
am
     dz
  gn
    lg
  pap
    ti
an
     ee
  gu
    li
  pa_PK     tig
ar
     el
  gv
    lij
  pi
    tk
ar_LY
     en
  ha
    lld
  pl
    tl
arn
     en_AU
  hak
    ln
  pms
    tn
ary
     en@boldquot  haw
    lo
  ps
    to
ar_YE
     en_CA
  he
    locale.alias  pt
    tpi
as
     en_GB
  hi
    lt
  pt_BR     tr
ast
     en_IN
  hif
    lv
  pt_PT     trv
ay
     en@quot
  hr
    lzh
  quz
    ts
az
     en@shaw
  hsb
    mai
  raj
    tt
az_AZ
     en_US
  ht
    mg
  ro
    tt@iqtelif
az_IR
     eo
  hu
    mhr
  ro_MD     tzm
ba
     es
  hy
    mi
  ru
    ug
bar
     es_AR
  hy_AM     mjw
  ru_UA     uk
be
     es_CL
  hye
    mk
  rw
    ur
be@latin     es_CO
  hye_RU    ml
  sa
    ur_PK
bem
     es_CR
  ia
    mn
  sah
    uz
ber
     es_DO
  id
    mnw
  sc
    uz@cyrillic
be@tarask    es_EC
  ie
    mr
  sd
    uz@Latn
bg
     es_ES
  ig
    ms
  sdh
    ve
bi
     es_MX
  ik
    ms@Arab
  se
    vec
bn
     es_NI
  io
    mt
  sgs
    vi
bn_BD
     es_PA
  is
    my
  shn
    wa
bn_IN
     es_PE
  it
    na
  shs
    wae
bo
     es_PR
  it_CARES  nah
  si
    wal
br
     es_SV
  iu
    nan
  sk
    wo
bs
     es_UY
  ja
    nb
  sl
    xh
byn
     es_VE
  ja_JP     nb_NO
  sm
    yi
ca
     et
  jam
    nds
  sn
    yo
ca@valencia  eu
  jv
    nds_NL
  so
    yue
ce
     fa
  ka
    ne
  son
    zgh
ch
     fa_AF
  kab
    niu
  sq
    zh
chr
     fa_IR
  ki
    nl
  sr
    zh_CN
ckb
     ff
  kk
    nl_BE
  sr@latin  zh_Hans
cmn
     fi
  kl
    nn
  sr_ME     zh_Hant
co
     fil
  km
    nn_NO
  ss
    zh_HK
crh
     fo
  kmr
    no
  st
    zh_LATN@pinyin
cs
     fr
  kn
    nqo
  su
    zh_SG
csb
     fr_CA
  ko
    nr
  sv
    zh_TW
cv
     fr_CH
  kok
    nso
  sw
    zu
cy
     fr_FR
  ks
    nv
  syr
da
     frp
  ku
    ny
  szl

Page 17: Page 17

To log a message, we use an incorrect command:

=> select1;

ERROR:  syntax error at or near "select1"
LINE 1: select1;
        ^

student$ tail -n 2 /var/log/postgresql/postgresql-16-main.log

2026-06-11 17:36:00.120 MSK [37084] student@admin_localization_utf8 ERROR:  syntax error
at or near "select1" at character 1
2026-06-11 17:36:00.120 MSK [37084] student@admin_localization_utf8 STATEMENT:  select1;

The server log records the same error message and the failed command itself. To display messages in French, change the lc_messages parameter.

=> SET lc_messages TO 'fr_FR.UTF8';

SET

=> select1;

ERREUR:  erreur de syntaxe sur ou près de « select1 »
LINE 1: select1;
        ^

student$ tail -n 2 /var/log/postgresql/postgresql-16-main.log

2026-06-11 17:36:00.191 MSK [37084] student@admin_localization_utf8 ERREUR:  erreur de
syntaxe sur ou près de « select1 » au caractère 1
2026-06-11 17:36:00.191 MSK [37084] student@admin_localization_utf8 INSTRUCTION :
select1;

Now the message is displayed to the client and logged in French. Look at the messages displayed by the psql client. They remain in English:

=> \dt

Did not find any relations.

The language for messages from a client built with NLS support is determined by the LC_MESSAGES and LANGUAGE OS environment variables, with LANGUAGE taking precedence:

=> \! echo $LC_MESSAGES

=> \! echo $LANGUAGE

To fully localize the environment, set French for psql messages.

=> \q

student$ export LANGUAGE=fr_FR

student$ psql -d admin_localization_utf8

=> SET lc_messages TO 'fr_FR.UTF8';

SET

Now all messages are displayed in French.

=> \dt

Aucune relation n'a été trouvée.

=> select1;

ERREUR:  erreur de syntaxe sur ou près de « select1 »
LIGNE 1 : select1;
          ^

Page 18: 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/collation

Page 19: libc Provider

Character order

• depends on the implementation in the OS
• library version is tracked
• Used by default
  • for the cluster and database

Speaker notes

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.

Page 20: 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/collation#ICU-CUSTOM-COLLA TIONS

https://icu.unicode.org/

Page 21: Collations

Let’s unset the variable and restart psql to return to English.

student$ unset LANGUAGE

student$ psql -d admin_localization_utf8

During cluster initialization, special objects called collations are created in the system catalog of each database, based on the information about locales installed in the OS. Initial collations in the database:

=> SELECT CASE collprovider
    WHEN 'd' THEN 'default'
    WHEN 'c' THEN 'libc'
    WHEN 'i' then 'icu'
  END,
  count(*)
FROM pg_collation
GROUP BY collprovider;

  case   | count
---------+-------
 icu     |   853
 libc    |    50
 default |     1
(3 rows)

Collations for French obtained from the OS:

=> \dOS+ fr*

                                                             List of collations
   Schema   |    Name     | Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |
Deterministic? |            Description
------------+-------------+----------+------------+------------+------------+-----------+-
---------------+-----------------------------------
 pg_catalog | fr-BE-x-icu | icu      |            |            | fr-BE      |           |
yes            | French (Belgium)
 pg_catalog | fr-BF-x-icu | icu      |            |            | fr-BF      |           |
yes            | French (Burkina Faso)
 pg_catalog | fr-BI-x-icu | icu      |            |            | fr-BI      |           |
yes            | French (Burundi)
 pg_catalog | fr-BJ-x-icu | icu      |            |            | fr-BJ      |           |
yes            | French (Benin)
 pg_catalog | fr-BL-x-icu | icu      |            |            | fr-BL      |           |
yes            |
 pg_catalog | fr-CA-x-icu | icu      |            |            | fr-CA      |           |
yes            | French (Canada)
 pg_catalog | fr-CD-x-icu | icu      |            |            | fr-CD      |           |
yes            | French (Congo - Kinshasa)
 pg_catalog | fr-CF-x-icu | icu      |            |            | fr-CF      |           |
yes            | French (Central African Republic)
 pg_catalog | fr-CG-x-icu | icu      |            |            | fr-CG      |           |
yes            | French (Congo - Brazzaville)
 pg_catalog | fr-CH-x-icu | icu      |            |            | fr-CH      |           |
yes            | French (Switzerland)
 pg_catalog | fr-CI-x-icu | icu      |            |            | fr-CI      |           |
yes            |
 pg_catalog | fr-CM-x-icu | icu      |            |            | fr-CM      |           |
yes            | French (Cameroon)
 pg_catalog | fr-DJ-x-icu | icu      |            |            | fr-DJ      |           |
yes            | French (Djibouti)
 pg_catalog | fr-DZ-x-icu | icu      |            |            | fr-DZ      |           |
yes            | French (Algeria)
 pg_catalog | fr-FR-x-icu | icu      |            |            | fr-FR      |           |
yes            | French (France)
 pg_catalog | fr-GA-x-icu | icu      |            |            | fr-GA      |           |
yes            | French (Gabon)
 pg_catalog | fr-GF-x-icu | icu      |            |            | fr-GF      |           |
yes            | French (French Guiana)
 pg_catalog | fr-GN-x-icu | icu      |            |            | fr-GN      |           |
yes            | French (Guinea)
 pg_catalog | fr-GP-x-icu | icu      |            |            | fr-GP      |           |
yes            | French (Guadeloupe)

Page 22: Page 22

 pg_catalog | fr-GQ-x-icu | icu      |            |            | fr-GQ      |           |
yes            | French (Equatorial Guinea)
 pg_catalog | fr-HT-x-icu | icu      |            |            | fr-HT      |           |
yes            | French (Haiti)
 pg_catalog | fr-KM-x-icu | icu      |            |            | fr-KM      |           |
yes            | French (Comoros)
 pg_catalog | fr-LU-x-icu | icu      |            |            | fr-LU      |           |
yes            | French (Luxembourg)
 pg_catalog | fr-MA-x-icu | icu      |            |            | fr-MA      |           |
yes            | French (Morocco)
 pg_catalog | fr-MC-x-icu | icu      |            |            | fr-MC      |           |
yes            | French (Monaco)
 pg_catalog | fr-MF-x-icu | icu      |            |            | fr-MF      |           |
yes            | French (St. Martin)
 pg_catalog | fr-MG-x-icu | icu      |            |            | fr-MG      |           |
yes            | French (Madagascar)
 pg_catalog | fr-ML-x-icu | icu      |            |            | fr-ML      |           |
yes            | French (Mali)
 pg_catalog | fr-MQ-x-icu | icu      |            |            | fr-MQ      |           |
yes            | French (Martinique)
 pg_catalog | fr-MR-x-icu | icu      |            |            | fr-MR      |           |
yes            | French (Mauritania)
 pg_catalog | fr-MU-x-icu | icu      |            |            | fr-MU      |           |
yes            | French (Mauritius)
 pg_catalog | fr-NC-x-icu | icu      |            |            | fr-NC      |           |
yes            | French (New Caledonia)
 pg_catalog | fr-NE-x-icu | icu      |            |            | fr-NE      |           |
yes            | French (Niger)
 pg_catalog | fr-PF-x-icu | icu      |            |            | fr-PF      |           |
yes            | French (French Polynesia)
 pg_catalog | fr-PM-x-icu | icu      |            |            | fr-PM      |           |
yes            | French (St. Pierre & Miquelon)
 pg_catalog | fr-RE-x-icu | icu      |            |            | fr-RE      |           |
yes            |
 pg_catalog | fr-RW-x-icu | icu      |            |            | fr-RW      |           |
yes            | French (Rwanda)
 pg_catalog | fr-SC-x-icu | icu      |            |            | fr-SC      |           |
yes            | French (Seychelles)
 pg_catalog | fr-SN-x-icu | icu      |            |            | fr-SN      |           |
yes            | French (Senegal)
 pg_catalog | fr-SY-x-icu | icu      |            |            | fr-SY      |           |
yes            | French (Syria)
 pg_catalog | fr-TD-x-icu | icu      |            |            | fr-TD      |           |
yes            | French (Chad)
 pg_catalog | fr-TG-x-icu | icu      |            |            | fr-TG      |           |
yes            | French (Togo)
 pg_catalog | fr-TN-x-icu | icu      |            |            | fr-TN      |           |
yes            | French (Tunisia)
 pg_catalog | fr-VU-x-icu | icu      |            |            | fr-VU      |           |
yes            | French (Vanuatu)
 pg_catalog | fr-WF-x-icu | icu      |            |            | fr-WF      |           |
yes            | French (Wallis & Futuna)
 pg_catalog | fr-YT-x-icu | icu      |            |            | fr-YT      |           |
yes            | French (Mayotte)
 pg_catalog | fr-x-icu    | icu      |            |            | fr         |           |
yes            | French
 pg_catalog | fr_FR       | libc     | fr_FR.utf8 | fr_FR.utf8 |            |           |
yes            |
 pg_catalog | fr_FR.utf8  | libc     | fr_FR.utf8 | fr_FR.utf8 |            |           |
yes            |
(49 rows)

=> SELECT unnest(regexp_match(setting,'--with-icu')) config
FROM pg_config()
WHERE name = 'CONFIGURE';

   config
------------
 --with-icu
(1 row)

Page 23: ICU Collations

The sorting order for the default collation differs from the order for fr-CA-x-icu. Accents are usually compared left-to-right, but in Canadian French they are compared right-to-left:

=> WITH t(c) AS (
  VALUES('coté'),('côte')
)
SELECT string_agg(t.c,',' ORDER BY t.c) AS "default",
       string_agg(t.c,',' ORDER BY t.c COLLATE "fr-CA-x-icu") AS "fr-CA-x-icu"
FROM t \gx

-[ RECORD 1 ]----------
default     | coté,côte
fr-CA-x-icu | côte,coté

The COLLATE keyword following an expression (in ORDER BY) explicitly specifies the collation. By default, the collation set in database locale parameters is used. ICU library capabilities allow flexible customization of sorting for specific character groups by defining new collations with parameters specified as part of a language tag. Default sort places digits and letters after currency symbols:

=> WITH t(c) AS (
  VALUES('a'),('b'),('c'),('$'),('€'),('¥'),('1'),('2'),('3')
)
SELECT string_agg(t.c,',' ORDER BY t.c)
FROM t \gx

-[ RECORD 1 ]-----------------
string_agg | $,¥,€,1,2,3,a,b,c

Create a new collation where currencies come after digits:

=> CREATE COLLATION digit_cur
  (provider = icu, locale = 'fr-FR-u-kr-digit-currency');

CREATE COLLATION

And another collation where, in addition to the previous one, letters come before digits:

=> CREATE COLLATION latn_digit_cur
  (provider = icu, locale = 'fr-FR-u-kr-latn-digit-currency');

CREATE COLLATION

Check the sorting:

=> WITH t(c) AS (
  VALUES('a'),('b'),('c'),('$'),('€'),('¥'),('1'),('2'),('3')
)
SELECT string_agg(t.c,',' ORDER BY t.c) AS "default",
       string_agg(t.c,',' ORDER BY t.c COLLATE "digit_cur") AS "digit_cur",
       string_agg(t.c,',' ORDER BY t.c COLLATE "latn_digit_cur") AS "latn_digit_cur"
FROM t \gx

-[ RECORD 1 ]--+------------------
default        | $,¥,€,1,2,3,a,b,c
digit_cur      | 1,2,3,$,¥,€,a,b,c
latn_digit_cur | a,b,c,1,2,3,$,¥,€

If the collation parameters mentioned above do not meet your needs, you can customize the sort order by defining specific collations:

=> CREATE COLLATION initials
  (provider = icu, locale = 'ru', rules = '& R < O < Y < G < B < I < V');

CREATE COLLATION

=> WITH initials(name) AS (
  VALUES ('Richard'), ('Of'), ('York'), ('Gave'), ('Battle'), ('In'), ('Vain')
)
SELECT string_agg(name, ' ' ORDER BY name) AS incorrect,
       string_agg(name, ' ' ORDER BY name COLLATE initials) AS correct
FROM initials \gx

Page 24: Page 24

-[ RECORD 1 ]----------------------------------
incorrect | Battle Gave In Of Richard Vain York
correct   | Richard Of York Gave Battle In Vain

Page 25: 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

Speaker notes

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.

Page 26: Nondeterministic Collations

Define a nondeterministic collation for case-insensitive sorting:

=> CREATE COLLATION ignore_case
  (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

CREATE COLLATION

Now strings in different cases can be equal:

=> SELECT
'postgres' = 'POSTGRES' AS "default",
'postgres' = 'POSTGRES' COLLATE "ignore_case" AS "ignore_case"
;

 default | ignore_case
---------+-------------
 f       | t
(1 row)

However, pattern matching is not possible for non-deterministic collations:

=> SELECT 'PostgreSQL' LIKE 'post%' COLLATE "ignore_case";

ERROR:  nondeterministic collations are not supported for LIKE

Page 27: 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

Speaker notes

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.

Page 28: Changes in Provider Libraries

When a collation is created, the library version reported by the OS is stored in the system catalog. If the library version changes in the OS, a warning about the version mismatch will be issued every time the collation is used. You can compare versions with a query:

=> SELECT c.collname,
  c.collversion AS version,
  pg_collation_actual_version(c.oid) AS actual_version,
  c.collprovider
FROM pg_collation c
WHERE c.collname LIKE 'en_US%';

        collname        |   version    | actual_version | collprovider
------------------------+--------------+----------------+--------------
 en_US.utf8             | 2.39         | 2.39           | c
 en_US                  | 2.39         | 2.39           | c
 en-US-x-icu            | 153.121      | 153.121        | i
 en-US-u-va-posix-x-icu | 153.121.44.8 | 153.121.44.8   | i
(4 rows)

Changes in a collation can lead to incorrect index operation. If this happens, it is recommended to rebuild indexes using the new version of the collation. After that, you can update the version in the system catalog using the ALTER COLLATION … REFRESH VERSION, and warnings will no longer be displayed. Query to get a list of objects that depend on collations requiring an update:

=> SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
  pg_describe_object(classid, objid, objsubid) AS "Object"
FROM pg_depend d
  JOIN pg_collation c ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
WHERE c.collversion <> pg_collation_actual_version(c.oid)
ORDER BY 1, 2;

 Collation | Object
-----------+--------
(0 rows)

The same check is performed for collations at the database level. The pg_database_collation_actual_version function shows the current collation version in the OS:

=> SELECT d.datname,
  d.datcollversion AS version,
  pg_database_collation_actual_version(d.oid) AS actual_version,
  datlocprovider
FROM pg_database d;

          datname          | version | actual_version | datlocprovider
---------------------------+---------+----------------+----------------
 postgres                  | 2.39    | 2.39           | c
 student                   | 2.39    | 2.39           | c
 template1                 | 2.39    | 2.39           | c
 template0                 |         | 2.39           | c
 admin_localization_utf8   | 2.39    | 2.39           | c
 admin_localization_latin1 | 153.121 | 153.121        | i
(6 rows)

If the version of the collation set for the database does not match the library version in the OS, a warning is issued when connecting to the database. In this case, it is recommended to rebuild indexes that use the default collation. After that, you can update the version number stored in pg_database using the ALTER DATABASE … REFRESH COLLATION VERSION.

Page 29: 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

Page 30: 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?

Speaker notes

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:

https://postgrespro.com/docs/postgresql/16/functions-formatting#FUNCTION S-FORMATTING-DATETIME-TABLE

Page 31: 1. Database Encodings

Let’s check that the OS has locales with iso88591 encoding:

=> \! locale -a | grep iso88591

fr_FR.iso88591

Create databases with LATIN1 and UTF8 encodings:

=> CREATE DATABASE admin_localization_latin1
  TEMPLATE template0
  ENCODING 'LATIN1'
  LOCALE 'fr_FR.iso88591';

CREATE DATABASE

=> CREATE DATABASE admin_localization_utf8;

CREATE DATABASE

=> \x \l admin_localization_* \x

Expanded display is on.
List of databases
-[ RECORD 1 ]-----+--------------------------
Name              | admin_localization_latin1
Owner             | student
Encoding          | LATIN1
Locale Provider   | libc
Collate           | fr_FR.iso88591
Ctype             | fr_FR.iso88591
ICU Locale        |
ICU Rules         |
Access privileges |
-[ RECORD 2 ]-----+--------------------------
Name              | admin_localization_utf8
Owner             | student
Encoding          | UTF8
Locale Provider   | libc
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
ICU Locale        |
ICU Rules         |
Access privileges |

Expanded display is off.

Connect to the database with LATIN1 encoding:

=> \c admin_localization_latin1

You are now connected to database "admin_localization_latin1" as user "student".

=> SET client_encoding = 'UTF8';

SET

Check that the client and server use different encodings:

=> SELECT name, setting
FROM pg_settings
WHERE name LIKE '%encoding';

      name       | setting
-----------------+---------
 client_encoding | UTF8
 server_encoding | LATIN1
(2 rows)

Create a table containing a string with French accents:

=> CREATE TABLE tab (col) AS
  VALUES ('éléphant'),('phénomène'),('théâtre'),('tête');

SELECT 4

=> SELECT * FROM tab;

Page 32: Page 32

    col
-----------
 éléphant
 phénomène
 théâtre
 tête
(4 rows)

=> \q

Create a logical backup:

student$ pg_dump -d admin_localization_latin1 -Fc -f latin1.dump

The backup content is exported in the database encoding (LATIN1), and the file begins with a command that sets the client_encoding parameter to match that same LATIN1 value. Restore the table into the admin_localization_utf8 database:

student$ pg_restore latin1.dump -d admin_localization_utf8 -t tab

Because client_encoding is set during restoration, characters are automatically converted. Let’s check that the French accents were transferred correctly:

student$ psql -d admin_localization_utf8

=> SELECT * FROM tab;

    col
-----------
 éléphant
 phénomène
 théâtre
 tête
(4 rows)

2. Get the Day of the Week Number

Current date and time localization settings:

=> SHOW lc_time;

   lc_time
-------------
 en_US.UTF-8
(1 row)

There are two format patterns for getting the day of the week number: ID — week starts on Monday D — week starts on Sunday

=> SELECT to_char(current_date, 'TMDay: ID') AS "ID",
          to_char(current_date, 'TMDay: D') AS "D" ;

     ID      |      D
-------------+-------------
 Thursday: 4 | Thursday: 5
(1 row)

The day of the week number does not depend on localization settings, including the lc_time parameter:

=> SET lc_time TO 'fr_FR.utf8';

SET

=> SELECT to_char(current_date, 'TMDay: ID') AS "ID",
          to_char(current_date, 'TMDay: D') AS "D" ;

    ID    |    D
----------+----------
 Jeudi: 4 | Jeudi: 5
(1 row)