Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ugKy0-001Uwe-S8 for pgsql-docs@arkaria.postgresql.org; Mon, 28 Jul 2025 10:20:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ugKxz-006SMJ-II for pgsql-docs@arkaria.postgresql.org; Mon, 28 Jul 2025 10:20:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ugKxz-006SMA-6n for pgsql-docs@lists.postgresql.org; Mon, 28 Jul 2025 10:20:23 +0000 Received: from mail-ed1-x529.google.com ([2a00:1450:4864:20::529]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ugKxv-001HOO-2B for pgsql-docs@lists.postgresql.org; Mon, 28 Jul 2025 10:20:23 +0000 Received: by mail-ed1-x529.google.com with SMTP id 4fb4d7f45d1cf-605b9488c28so7220198a12.2 for ; Mon, 28 Jul 2025 03:20:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1753698019; x=1754302819; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=BszSVojjP1/QBniduuPJ+0DqkZ4yFV4rqOfpbCZNwdw=; b=IGGatVvnaRpde/vPTTKBZuVYPCMowgad6vvKrGiT84Cfp0sWkDgwth9UD9U2uKadDW cDN+VajTPO8RCayeoFYqgU+DGecjZM36erIfUZBxsfIn0w9VaJwjot7MadyM3ldSmQQm crcCFPPKAMfRVJdy6LsS/eNsDGdZwew8wS5Ti/IpIELQvA/OBYkJAHIgk+gFauaIPHZc qyADOCftw548U/LMORGyIm2c1Gz2im1/De99ERQLQwgv1iLNWYKAaVu48omPfSgWEjrT gCecTrYzMrtqfS/sSK3N+j2Se178VTnYtR+z/sYyddJQ8PY3Dk6QYZM43t6pQZoFtm+3 svBQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1753698019; x=1754302819; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=BszSVojjP1/QBniduuPJ+0DqkZ4yFV4rqOfpbCZNwdw=; b=aBbnaw/PiczmlCk/hv2awz0y352tvrrGZ9/0TI3NBB43chfH7LDfYwUTl6xH4buliU 1d2vvsO9P4xVzPi0tbCWXsnLhcEqj5CdzSwqwrgdTdaYhLI/WLb+XMZ79N21xlAjtz9M aE+srtT7hvK6WUQA487zrKzShJFx0FbHbcksAlLosI502G4NzgJ4roD07brr+90pPGue x8BGxadZ4k6dtsB4FM1jFvS8aL23A8/A3kj4u7sO4+Tscl+PjjPm/EcHzrKYZv/ah/d3 MMpyzAyTjJPdVYnpUULLRjYzQWYoL5bWnU9uAyUX7vN772akMflwKa+9hkgaaQUk751C SUIg== X-Gm-Message-State: AOJu0YymxXOOqVhX55v6R0ui4M93bB/QDz4dw7KaSgkTwaSWBNnwH9j+ fam6RNqgYNod0v4sQXhyt3G0aTkKlgJCxYixc4NRGMcKto3QzUVBxdzP X-Gm-Gg: ASbGnctsPtzo7nrd0vsd0ZWxBMEeajOItqXuorPTTgEV+ixyZU5CpE6kxNSis448g6I umROydI8kPHy9bxqgmgd5d7tabukZQs13rZ6iJ4rb9/YhDkcTErZCDEmKad+NKIo3IHfFBb+83J b/9yObhlHostSPto0EloNRmVYS3bNgZD2cCzcl2Xeivz+iH15WiNN37uHI+QPRyfcSX/nHi65JH cnxUzk2YZGRWxx0LXt7Drqx7DBre8oaiBolag0GqFS1qNNNhDlAkbnR0+gSKUgWmdfUaKuOSexs C40E6DpvIKXQ3FC/9vrfZOVTQ8qkOP/2MCEHhLsuZCoWNiKIyWK1FM5QNE3xqrmtOE5TsFEtqYH QSsxPuYD6jVzKEWH0frMUSLKEuRlwUyjaZhnVjrk= X-Google-Smtp-Source: AGHT+IH/ns5dNpH1BypTjJ0RPa0gX7y1koxhld3OrcX7BzxM+erLPco4/ljkGcsDOm2BrIW0u1n2ww== X-Received: by 2002:a17:906:6a0b:b0:ae6:e688:3269 with SMTP id a640c23a62f3a-af61d37c3fcmr1266971366b.42.1753698018424; Mon, 28 Jul 2025 03:20:18 -0700 (PDT) Received: from smtpclient.apple ([212.122.72.44]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-af635a6314asm401777366b.91.2025.07.28.03.20.17 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 28 Jul 2025 03:20:17 -0700 (PDT) From: Alexander Korotkov Message-Id: <0658C8F0-5ED4-4962-A2A3-524B0D899982@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_46933886-F643-4F55-8660-BE346B1D4262" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51.11.2\)) Subject: Re: Initcap works differently with different locale providers Date: Mon, 28 Jul 2025 13:20:06 +0300 In-Reply-To: <804cc10ef95d4d3b298e76b181fd9437@postgrespro.ru> Cc: pgsql-docs@lists.postgresql.org To: Oleg Tselebrovskiy References: <804cc10ef95d4d3b298e76b181fd9437@postgrespro.ru> X-Mailer: Apple Mail (2.3776.700.51.11.2) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_46933886-F643-4F55-8660-BE346B1D4262 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi, Oleg! > On 25 Sep 2024, at 18:13, Oleg Tselebrovskiy = wrote: >=20 > Greetings, everyone! >=20 > One of our clients has found a difference in behaviour of initcap = function when > using different locale providers, shown below >=20 > postgres=3D# create database test_db_1 locale_provider=3Dicu = locale=3D"ru_RU.UTF-8" template=3Dtemplate0; > NOTICE: using standard form "ru-RU" for ICU locale = "ru_RU.UTF-8" > CREATE DATABASE > postgres=3D# \c test_db_1; > You are now connected to database "test_db_1" as user = "postgres". > test_db_1=3D# select initcap('=D0=A7=D0=B8=D0=AE =D0=90.=D0=AE.');= > initcap > ---------- > =D0=A7=D0=B8=D1=8E =D0=90.=D1=8E. > (1 row) > test_db_1=3D# select initcap('joHn d.e.'); > initcap > ----------- > John D.e. > (1 row) > postgres=3D# create database test_db_2 locale_provider=3Dlibc = locale=3D"ru_RU.UTF-8" template=3Dtemplate0; > CREATE DATABASE > postgres=3D# \c test_db_2 > You are now connected to database "test_db_2" as user = "postgres". > test_db_2=3D# select initcap('=D0=A7=D0=B8=D0=AE =D0=90.=D0=AE.');= > initcap > ---------- > =D0=A7=D0=B8=D1=8E =D0=90.=D0=AE. > (1 row) > test_db_2=3D# select initcap('joHn d.e.'); > initcap > ----------- > John D.E. > (1 row) >=20 > And an easier reproduction (should work for REL_12_STABLE and up) >=20 > postgres=3D# SELECT initcap('first.second' COLLATE "en-x-icu"); > initcap > -------------- > First.second > (1 row) > postgres=3D# SELECT initcap('first.second' COLLATE "en_US"); > initcap > -------------- > First.Second > (1 row) >=20 > This behaviour is reproducible on REL_12_STABLE and up to master >=20 > I don't believe that this is an erroneous behaviour, just a differing = one, hence > just a documentation change proposition >=20 > 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 >=20 > 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=C2=AE Standard Annex #29 = [1] >=20 > Similar issue was briefly discussed in [2] >=20 > The suggested documentation patch is attached (versions for = REL_13_STABLE+ and > for REL_12_STABLE only) >=20 > [1]: https://www.unicode.org/reports/tr29/#Word_Boundaries > [2]: = https://www.postgresql.org/message-id/CAEwbS1R8pwhRkwRo3XsPt24ErBNtFWuReAZ= hVPJwA3oqo148tA%40mail.gmail.com >=20 > Oleg Tselebrovskiy, Postgres = Professional I can confirm inicap works with libc and libicu as you stated. The = documentation patch looks good to me. I=E2=80=99ve written a commit = message. The REL_12_STABLE branch is not relevant anymore as it=E2=80=99s= out of support. I=E2=80=99m going to push this if no objections. ------ Regards, Alexander Korotkov Supabase =EF=BF=BC= --Apple-Mail=_46933886-F643-4F55-8660-BE346B1D4262 Content-Type: multipart/mixed; boundary="Apple-Mail=_6852A17B-6127-49F0-9BE9-0A03FB6D6D69" --Apple-Mail=_6852A17B-6127-49F0-9BE9-0A03FB6D6D69 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 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=3D# create database test_db_1 locale_provider=3Dicu = locale=3D"ru_RU.UTF-8" template=3Dtemplate0;
NOTICE: =  using standard form "ru-RU" for ICU locale "ru_RU.UTF-8"
CREATE = DATABASE
= postgres=3D# \c test_db_1;
You are now connected to database = "test_db_1" as user "postgres".
test_db_1=3D# select = initcap('=D0=A7=D0=B8=D0=AE =D0=90.=D0=AE.');
= initcap
----------
=D0=A7=D0=B8= =D1=8E =D0=90.=D1=8E.
(1 row)
= test_db_1=3D# select initcap('joHn d.e.');
= initcap
-----------
John = D.e.
= (1 row)
postgres=3D# create database = test_db_2 locale_provider=3Dlibc locale=3D"ru_RU.UTF-8" = template=3Dtemplate0;
CREATE DATABASE
= postgres=3D# \c test_db_2
You are now connected to database = "test_db_2" as user "postgres".
test_db_2=3D# select = initcap('=D0=A7=D0=B8=D0=AE =D0=90.=D0=AE.');
= initcap
----------
=D0=A7=D0=B8= =D1=8E =D0=90.=D0=AE.
(1 row)
= test_db_2=3D# select initcap('joHn d.e.');
= initcap
-----------
John = D.E.
= (1 row)

And an easier reproduction (should work for = REL_12_STABLE and up)

postgres=3D# SELECT = initcap('first.second' COLLATE "en-x-icu");
= initcap
--------------
= First.second
(1 row)
= postgres=3D# 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=C2=AE 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/CAEwbS1R8pwhRkwRo3XsPt24ErBNtFWuReAZ= hVPJwA3oqo148tA%40mail.gmail.com

Oleg Tselebrovskiy, Postgres = Professional<v1-0001-string-functio= ns.patch><v1-0002-string-functio= ns-REL_12.patch>

I can = confirm inicap works with libc and libicu as you stated.  The = documentation patch looks good to me.  I=E2=80=99ve written a = commit message.  The REL_12_STABLE branch is not relevant anymore = as it=E2=80=99s out of support.  I=E2=80=99m going to push this if = no objections.

------
Regards,
Alexander Korotkov
Supabase
= --Apple-Mail=_6852A17B-6127-49F0-9BE9-0A03FB6D6D69 Content-Disposition: attachment; filename=v2-0001-Clarify-documentation-for-the-initcap-function.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-Clarify-documentation-for-the-initcap-function.patch" Content-Transfer-Encoding: quoted-printable =46rom=201e6631804d6e56003dd1c7ed04458bd3d6f7b9f3=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Alexander=20Korotkov=20= =0ADate:=20Mon,=2028=20Jul=202025=2013:06:26=20= +0300=0ASubject:=20[PATCH=20v2]=20Clarify=20documentation=20for=20the=20= initcap=20function=0A=0AThis=20commit=20documents=20differences=20in=20= the=20definition=20of=20word=20separators=20for=0Athe=20initcap=20= function=20between=20libc=20and=20ICU=20locale=20providers.=0ABackpatch=20= to=20all=20supported=20branches.=0A=0ADiscussion:=20= https://postgr.es/m/804cc10ef95d4d3b298e76b181fd9437%40postgrespro.ru=0A= Author:=20Oleg=20Tselebrovskiy=20=0A= Backpatch-through:=2013=0A---=0A=20doc/src/sgml/func.sgml=20|=207=20= +++++--=0A=201=20file=20changed,=205=20insertions(+),=202=20deletions(-)=0A= =0Adiff=20--git=20a/doc/src/sgml/func.sgml=20b/doc/src/sgml/func.sgml=0A= index=20de5b5929ee0..64ce2b448e6=20100644=0A---=20= a/doc/src/sgml/func.sgml=0A+++=20b/doc/src/sgml/func.sgml=0A@@=20-3148,8=20= +3148,11=20@@=20SELECT=20NOT(ROW(table.*)=20IS=20NOT=20NULL)=20FROM=20= TABLE;=20--=20detect=20at=20least=20one=20null=20in=0A=20=20=20=20=20=20=20= =20=0A=20=20=20=20=20=20=20=20=0A=20=20=20=20=20=20=20=20=20= Converts=20the=20first=20letter=20of=20each=20word=20to=20upper=20case=20= and=20the=0A-=20=20=20=20=20=20=20=20rest=20to=20lower=20case.=20Words=20= are=20sequences=20of=20alphanumeric=0A-=20=20=20=20=20=20=20=20= characters=20separated=20by=20non-alphanumeric=20characters.=0A+=20=20=20= =20=20=20=20=20rest=20to=20lower=20case.=20When=20using=20the=20= libc=20locale=0A+=20=20=20=20=20=20=20=20provider,=20= words=20are=20sequences=20of=20alphanumeric=20characters=20separated=0A+=20= =20=20=20=20=20=20=20by=20non-alphanumeric=20characters;=20when=20using=20= the=20ICU=20locale=20provider,=0A+=20=20=20=20=20=20=20=20words=20are=20= separated=20according=20to=0A+=20=20=20=20=20=20=20=20Unicode=C2=AE= =20Standard=20Annex=20#29.=0A=20=20=20=20=20=20=20=20=0A=20= =20=20=20=20=20=20=20=0A=20=20=20=20=20=20=20=20=20= initcap('hi=20THOMAS')=0A--=20=0A2.39.5=20(Apple=20= Git-154)=0A=0A= --Apple-Mail=_6852A17B-6127-49F0-9BE9-0A03FB6D6D69 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset=us-ascii --Apple-Mail=_6852A17B-6127-49F0-9BE9-0A03FB6D6D69-- --Apple-Mail=_46933886-F643-4F55-8660-BE346B1D4262--