Initcap works differently with different locale providers
Oleg Tselebrovskiy <o.tselebrovskiy@postgrespro.ru>
From: Oleg Tselebrovskiy <o.tselebrovskiy@postgrespro.ru>
To: pgsql-docs@lists.postgresql.org
Date: 2024-09-25T15:13:24Z
Lists: pgsql-docs
Attachments
- v1-0001-string-functions.patch (text/x-diff) patch v1-0001
- v1-0002-string-functions-REL_12.patch (text/x-diff) patch v1-0002
Greetings, everyone!
One of our clients has found a difference in behaviour of initcap
function when
using different locale providers, shown below
postgres=# create database test_db_1 locale_provider=icu
locale="ru_RU.UTF-8" template=template0;
NOTICE: using standard form "ru-RU" for ICU locale "ru_RU.UTF-8"
CREATE DATABASE
postgres=# \c test_db_1;
You are now connected to database "test_db_1" as user "postgres".
test_db_1=# select initcap('ЧиЮ А.Ю.');
initcap
----------
Чию А.ю.
(1 row)
test_db_1=# select initcap('joHn d.e.');
initcap
-----------
John D.e.
(1 row)
postgres=# create database test_db_2 locale_provider=libc
locale="ru_RU.UTF-8" template=template0;
CREATE DATABASE
postgres=# \c test_db_2
You are now connected to database "test_db_2" as user "postgres".
test_db_2=# select initcap('ЧиЮ А.Ю.');
initcap
----------
Чию А.Ю.
(1 row)
test_db_2=# select initcap('joHn d.e.');
initcap
-----------
John D.E.
(1 row)
And an easier reproduction (should work for REL_12_STABLE and up)
postgres=# SELECT initcap('first.second' COLLATE "en-x-icu");
initcap
--------------
First.second
(1 row)
postgres=# SELECT initcap('first.second' COLLATE "en_US");
initcap
--------------
First.Second
(1 row)
This behaviour is reproducible on REL_12_STABLE and up to master
I don't believe that this is an erroneous behaviour, just a differing
one, hence
just a documentation change proposition
I suggest adding a clarification that this function works differently
with libc
and ICU providers because there is a difference in what a "word" is
between them
In libc a word is a sequence of alphanumeric characters, separated by
non-alphanumeric characters (as it is written in documentation right
now)
In ICU words are divided according to Unicode® Standard Annex #29 [1]
Similar issue was briefly discussed in [2]
The suggested documentation patch is attached (versions for
REL_13_STABLE+ and
for REL_12_STABLE only)
[1]: https://www.unicode.org/reports/tr29/#Word_Boundaries
[2]:
https://www.postgresql.org/message-id/CAEwbS1R8pwhRkwRo3XsPt24ErBNtFWuReAZhVPJwA3oqo148tA%40mail.gmail.com
Oleg Tselebrovskiy, Postgres Professional
Commits
-
Further clarify documentation for the initcap function
- f8ce9ed220b5 19 (unreleased) landed
-
Revert "Clarify documentation for the initcap function"
- 0edaaa8f2978 14.19 landed
- 06633271ec40 13.22 landed
- 098c27dee4c1 15.14 landed
- b862465dba33 16.10 landed
- 3b3aa1f651c7 17.6 landed
- 2ae8280d16c9 18.0 landed
-
Clarify documentation for the initcap function
- 907ac2ca30bb 13.22 landed
- 276b022ec05b 14.19 landed
- 9af075c4e27e 15.14 landed
- d861b9282261 16.10 landed
- 3c05d4d3433e 17.6 landed
- 1fe9e3822c4e 18.0 landed
- c2c2c7e22566 19 (unreleased) landed