agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedFrom: Alexander Korotkov <aekorotkov@gmail.com>
To: Oleg Tselebrovskiy <o.tselebrovskiy@postgrespro.ru>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Initcap works differently with different locale providers
Date: Mon, 28 Jul 2025 13:20:06 +0300
Message-ID: <0658C8F0-5ED4-4962-A2A3-524B0D899982@gmail.com> (raw)
In-Reply-To: <804cc10ef95d4d3b298e76b181fd9437@postgrespro.ru>
References: <804cc10ef95d4d3b298e76b181fd9437@postgrespro.ru>
Hi, Oleg!
> On 25 Sep 2024, at 18:13, Oleg Tselebrovskiy <o.tselebrovskiy@postgrespro.ru> wrote:
>
> 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.gma...
>
> Oleg Tselebrovskiy, Postgres Professional<v1-0001-string-functions.patch><v1-0002-string-functions-REL_12.patch>
I can confirm inicap works with libc and libicu as you stated. The documentation patch looks good to me. I’ve written a commit message. The REL_12_STABLE branch is not relevant anymore as it’s out of support. I’m going to push this if no objections.
------
Regards,
Alexander Korotkov
Supabase
=
Attachments:
[application/octet-stream] v2-0001-Clarify-documentation-for-the-initcap-function.patch (1.6K, ../0658C8F0-5ED4-4962-A2A3-524B0D899982@gmail.com/3-v2-0001-Clarify-documentation-for-the-initcap-function.patch)
download | inline diff:
From 1e6631804d6e56003dd1c7ed04458bd3d6f7b9f3 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Mon, 28 Jul 2025 13:06:26 +0300
Subject: [PATCH v2] Clarify documentation for the initcap function
This commit documents differences in the definition of word separators for
the initcap function between libc and ICU locale providers.
Backpatch to all supported branches.
Discussion: https://postgr.es/m/804cc10ef95d4d3b298e76b181fd9437%40postgrespro.ru
Author: Oleg Tselebrovskiy <o.tselebrovskiy@postgrespro.ru>
Backpatch-through: 13
---
doc/src/sgml/func.sgml | 7 +++++--
1 file changed, 5 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index de5b5929ee0..64ce2b448e6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3148,8 +3148,11 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</para>
<para>
Converts the first letter of each word to upper case and the
- rest to lower case. Words are sequences of alphanumeric
- characters separated by non-alphanumeric characters.
+ rest to lower case. When using the <literal>libc</literal> locale
+ provider, words are sequences of alphanumeric characters separated
+ by non-alphanumeric characters; when using the ICU locale provider,
+ words are separated according to
+ <ulink url="https://www.unicode.org/reports/tr29/#Word_Boundaries">Unicode® Standard Annex #29</ulink>.
</para>
<para>
<literal>initcap('hi THOMAS')</literal>
--
2.39.5 (Apple Git-154)
=
view thread (16+ messages) latest in thread
Message-ID: <0658C8F0-5ED4-4962-A2A3-524B0D899982@gmail.com>
Permalink: ../0658C8F0-5ED4-4962-A2A3-524B0D899982@gmail.com/
Also on: postgresql.org/message-id/0658C8F0-5ED4-4962-A2A3-524B0D899982@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: aekorotkov@gmail.com, o.tselebrovskiy@postgrespro.ru, pgsql-docs@lists.postgresql.org
Subject: Re: Initcap works differently with different locale providers
In-Reply-To: <0658C8F0-5ED4-4962-A2A3-524B0D899982@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox