# Lesson 16: Administration / Localization > Native Markdown source page: Administration / Localization --- LLMS index: [llms.txt](/en/llms.txt) --- > [!NOTE] > This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. > **HTML source:** [dba2_16_admin_localization.html](https://edu.postgrespro.com/16/dba2-16-en/dba2_16_admin_localization.html) > **Archive:** [DBA2-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA2-handouts-16-en.zip) > **PDF pages:** 32 ## Page 1: Administration / Localization {#source-page-001} ### 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 {#source-page-002} - 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 {#source-page-003} **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 ARSET-SUPPORTED ## Page 4: Locales and their Categories {#source-page-004} **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 {#source-page-005} **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. 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. ## Page 6: Server Configuration {#source-page-006} ![Source slide page 6: Server Configuration](images/page-006.png){#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 {#source-page-007} The server OS already has locales installed with the French language support: ```sh student$ locale -a | grep fr ``` ```text french fr_FR fr_FR.iso88591 fr_FR.utf8 ``` The localization parameters for the OS session are controlled by environment variables: ```sh student$ locale ``` ```text 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: ```sh 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: ```sql => SELECT name, setting, context, sourcefile FROM pg_settings WHERE name LIKE 'lc%'; ``` ```text 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: ```sql => CREATE DATABASE admin_localization_utf8; ``` ```sql CREATE DATABASE ``` ## Page 8: Page 8 {#source-page-008} ```text => \x \list admin_localization_utf8|template1 \x ``` ```text 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 ``` ```text Expanded display is off. ``` ### Dates and LC\\\_TIME Configuration parameter for the corresponding locale category: ```text => SHOW lc_time; ``` ```text 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: ```sql => SELECT to_char(current_date, 'TMDay, DD TMMonth YYYY'); ``` ```text to_char ------------------------ Thursday, 11 June 2026 (1 row) ``` The same for French: ```text => SET lc_time = 'fr_FR.UTF8'; ``` ```text SET ``` ```sql => SELECT to_char(current_date, 'TMDay, DD TMMonth YYYY'); ``` ```text to_char --------------------- Jeudi, 11 Juin 2026 (1 row) ``` Reset the parameter to its original value: ```text => RESET lc_time; ``` ```text 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: ```sql => SET datestyle = 'Postgres'; SELECT current_setting('datestyle') AS datestyle, now(); ``` ```text SET datestyle | now ---------------+------------------------------------- Postgres, MDY | Thu Jun 11 17:35:59.079869 2026 MSK (1 row) ``` ## Page 9: Page 9 {#source-page-009} ```sql => SET datestyle = 'SQL, DMY'; SELECT current_setting('datestyle') AS datestyle, now(); ``` ```text SET datestyle | now -----------+-------------------------------- SQL, DMY | 11/06/2026 17:35:59.108653 MSK (1 row) ``` ```sql => SET datestyle = 'SQL, MDY'; SELECT current_setting('datestyle') AS datestyle, now(); ``` ```text SET datestyle | now -----------+-------------------------------- SQL, MDY | 06/11/2026 17:35:59.137287 MSK (1 row) ``` ```sql => RESET datestyle; SELECT current_setting('datestyle') AS datestyle, now(); ``` ```text 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: ```text => SET lc_numeric = 'fr_FR.UTF8'; ``` ```text SET ``` The following digit group separators (G) and decimal separators (D) are used in France: ```sql => SELECT to_char('12345'::numeric, '999G999D00'); ``` ```text to_char ------------- 12 345,00 (1 row) ``` The same for the United States: ```text => SET lc_numeric = 'en_US.UTF8'; ``` ```text SET ``` ```sql => SELECT to_char('12345'::numeric, '999G999D00'); ``` ```text to_char ------------- 12,345.00 (1 row) ``` ### Currency Amounts and LC\\\_MONETARY Configuration parameter for the corresponding locale category: ```text => SET lc_monetary = 'fr_FR.UTF8'; ``` ```text SET ``` The money data type adds a currency code to the amount: ```sql => SELECT '12345'::money; ``` ```text 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: ```text => SET lc_monetary = 'en_US.UTF8'; ``` ```text SET ``` ## Page 10: Page 10 {#source-page-010} ```sql => SELECT '12345'::money; ``` ```text money ------------ $12,345.00 (1 row) ``` ## Page 11: Client Configuration {#source-page-011} ![Source slide page 11: Client Configuration](images/page-011.png){#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 {#source-page-012} 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: ```sql => 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; ``` ```sql 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. ```text => \x \l admin_localization_latin1 \x ``` ```text 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 | ``` ```text Expanded display is off. ``` Connect to admin\_localization\_latin1 and check that character conversion is working properly. ```sql => \c admin_localization_latin1 ``` ```text You are now connected to database "admin_localization_latin1" as user "student". ``` Client and server encodings: ```sql => SELECT name, setting, context FROM pg_settings WHERE name LIKE '%encoding'; ``` ```text 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: ```text => \! locale | grep LC_CTYPE ``` ```text LC_CTYPE="en_US.UTF-8" ``` ```text => SET client_encoding = 'UTF-8'; ``` ```text SET ``` Now characters will be correctly converted from UTF8 to LATIN1 and back: ```sql => CREATE TABLE tab AS SELECT 'Oh là là'::text AS col; ``` ```sql SELECT 1 ``` ```sql => SELECT * FROM tab; ``` ```text col ---------- Oh là là (1 row) ``` ## Page 13: Page 13 {#source-page-013} List of available encoding conversions for LATIN1: ```text => \dcS iso_8859_1_to_*|*_to_iso_8859_1 ``` ```text 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) ``` ```sql => \c admin_localization_utf8 ``` ```text You are now connected to database "admin_localization_utf8" as user "student". ``` ## Page 14: Server Messages {#source-page-014} ![Source slide page 14: Server Messages](images/page-014.png){#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: ## Page 15: Utility Messages {#source-page-015} ![Source slide page 15: Utility Messages](images/page-015.png){#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 {#source-page-016} Server messages can be displayed in different languages. First, make sure that PostgreSQL was built with NLS support: ```sql => SELECT unnest(regexp_match(setting,'--enable-nls')) config FROM pg_config() WHERE name = 'CONFIGURE'; ``` ```text config -------------- --enable-nls (1 row) ``` List of available languages and their message files can be found here: ```sql => SELECT setting FROM pg_config() WHERE name = 'LOCALEDIR'; ``` ```text setting ------------------- /usr/share/locale (1 row) ``` ```sh student$ ls -C /usr/share/locale ``` ```text 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 {#source-page-017} To log a message, we use an incorrect command: ```text => select1; ``` ```text ERROR: syntax error at or near "select1" LINE 1: select1; ^ ``` ```sh student$ tail -n 2 /var/log/postgresql/postgresql-16-main.log ``` ```text 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. ```text => SET lc_messages TO 'fr_FR.UTF8'; ``` ```text SET ``` ```text => select1; ``` ```text ERREUR: erreur de syntaxe sur ou près de « select1 » LINE 1: select1; ^ ``` ```sh student$ tail -n 2 /var/log/postgresql/postgresql-16-main.log ``` ```text 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: ```sql => \dt ``` ```text 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: ```text => \! echo $LC_MESSAGES ``` ```text => \! echo $LANGUAGE ``` To fully localize the environment, set French for psql messages. ```sql => \q ``` ```sh student$ export LANGUAGE=fr_FR ``` ```sh student$ psql -d admin_localization_utf8 ``` ```text => SET lc_messages TO 'fr_FR.UTF8'; ``` ```text SET ``` Now all messages are displayed in French. ```sql => \dt ``` ```text Aucune relation n'a été trouvée. ``` ```text => select1; ``` ```text ERREUR: erreur de syntaxe sur ou près de « select1 » LIGNE 1 : select1; ^ ``` ## Page 18: Collations {#source-page-018} **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. ## Page 19: libc Provider {#source-page-019} **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 {#source-page-020} **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. TIONS ## Page 21: Collations {#source-page-021} Let’s unset the variable and restart psql to return to English. ```sh student$ unset LANGUAGE ``` ```sh 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: ```sql => SELECT CASE collprovider WHEN 'd' THEN 'default' WHEN 'c' THEN 'libc' WHEN 'i' then 'icu' END, count(*) FROM pg_collation GROUP BY collprovider; ``` ```text case | count ---------+------- icu | 853 libc | 50 default | 1 (3 rows) ``` Collations for French obtained from the OS: ```text => \dOS+ fr* ``` ```text 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 {#source-page-022} ```text 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) ``` > [!NOTE] > **Note** > Note that only collations compatible with the current database’s encoding are displayed, so the command output may vary for different databases within the same PostgreSQL instance. Collations for the ICU provider are created only if the server was built with --with-icu option. Such collations are named using a language tag with an “-x-icu” suffix to distinguish them from libc locales. ```sql => SELECT unnest(regexp_match(setting,'--with-icu')) config FROM pg_config() WHERE name = 'CONFIGURE'; ``` ```text config ------------ --with-icu (1 row) ``` ## Page 23: ICU Collations {#source-page-023} 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: ```sql => 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 ``` ```text -[ 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: ```sql => WITH t(c) AS ( VALUES('a'),('b'),('c'),('$'),('€'),('¥'),('1'),('2'),('3') ) SELECT string_agg(t.c,',' ORDER BY t.c) FROM t \gx ``` ```text -[ RECORD 1 ]----------------- string_agg | $,¥,€,1,2,3,a,b,c ``` Create a new collation where currencies come after digits: ```sql => CREATE COLLATION digit_cur (provider = icu, locale = 'fr-FR-u-kr-digit-currency'); ``` ```sql CREATE COLLATION ``` And another collation where, in addition to the previous one, letters come before digits: ```sql => CREATE COLLATION latn_digit_cur (provider = icu, locale = 'fr-FR-u-kr-latn-digit-currency'); ``` ```sql CREATE COLLATION ``` Check the sorting: ```sql => 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 ``` ```text -[ 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: ```sql => CREATE COLLATION initials (provider = icu, locale = 'ru', rules = '& R < O < Y < G < B < I < V'); ``` ```sql CREATE COLLATION ``` ```sql => 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 {#source-page-024} ```text -[ RECORD 1 ]---------------------------------- incorrect | Battle Gave In Of Richard Vain York correct | Richard Of York Gave Battle In Vain ``` ## Page 25: Determinism {#source-page-025} **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 {#source-page-026} Define a nondeterministic collation for case-insensitive sorting: ```sql => CREATE COLLATION ignore_case (provider = icu, locale = 'und-u-ks-level2', deterministic = false); ``` ```sql CREATE COLLATION ``` Now strings in different cases can be equal: ```sql => SELECT 'postgres' = 'POSTGRES' AS "default", 'postgres' = 'POSTGRES' COLLATE "ignore_case" AS "ignore_case" ; ``` ```text default | ignore_case ---------+------------- f | t (1 row) ``` However, pattern matching is not possible for non-deterministic collations: ```sql => SELECT 'PostgreSQL' LIKE 'post%' COLLATE "ignore_case"; ``` ```text ERROR: nondeterministic collations are not supported for LIKE ``` ## Page 27: Changes Tracking {#source-page-027} **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 {#source-page-028} 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: ```sql => 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%'; ``` ```text 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: ```sql => 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; ``` ```text 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: ```sql => SELECT d.datname, d.datcollversion AS version, pg_database_collation_actual_version(d.oid) AS actual_version, datlocprovider FROM pg_database d; ``` ```text 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 {#source-page-029} **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 {#source-page-030} 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: S-FORMATTING-DATETIME-TABLE ## Page 31: 1. Database Encodings {#source-page-031} Let’s check that the OS has locales with iso88591 encoding: ```text => \! locale -a | grep iso88591 ``` ```text fr_FR.iso88591 ``` Create databases with LATIN1 and UTF8 encodings: ```sql => CREATE DATABASE admin_localization_latin1 TEMPLATE template0 ENCODING 'LATIN1' LOCALE 'fr_FR.iso88591'; ``` ```sql CREATE DATABASE ``` ```sql => CREATE DATABASE admin_localization_utf8; ``` ```sql CREATE DATABASE ``` ```text => \x \l admin_localization_* \x ``` ```text 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 | ``` ```text Expanded display is off. ``` Connect to the database with LATIN1 encoding: ```sql => \c admin_localization_latin1 ``` ```text You are now connected to database "admin_localization_latin1" as user "student". ``` ```text => SET client_encoding = 'UTF8'; ``` ```text SET ``` Check that the client and server use different encodings: ```sql => SELECT name, setting FROM pg_settings WHERE name LIKE '%encoding'; ``` ```text name | setting -----------------+--------- client_encoding | UTF8 server_encoding | LATIN1 (2 rows) ``` Create a table containing a string with French accents: ```sql => CREATE TABLE tab (col) AS VALUES ('éléphant'),('phénomène'),('théâtre'),('tête'); ``` ```sql SELECT 4 ``` ```sql => SELECT * FROM tab; ``` ## Page 32: Page 32 {#source-page-032} ```text col ----------- éléphant phénomène théâtre tête (4 rows) ``` ```sql => \q ``` Create a logical backup: ```sh 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: ```sh 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: ```sh student$ psql -d admin_localization_utf8 ``` ```sql => SELECT * FROM tab; ``` ```text 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: ```text => SHOW lc_time; ``` ```text 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 ```sql => SELECT to_char(current_date, 'TMDay: ID') AS "ID", to_char(current_date, 'TMDay: D') AS "D" ; ``` ```text 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: ```text => SET lc_time TO 'fr_FR.utf8'; ``` ```text SET ``` ```sql => SELECT to_char(current_date, 'TMDay: ID') AS "ID", to_char(current_date, 'TMDay: D') AS "D" ; ``` ```text ID | D ----------+---------- Jeudi: 4 | Jeudi: 5 (1 row) ```