MIME-Version: 1.0
References: <f4aefa40-a25e-413f-a7be-66e4455c0337@manitou-mail.org>
 <0a4f41b84efa5d821aba166658fb845f1ab97f03.camel@j-davis.com>
 <CAHgHdKt+_+QhHK8WXQSoMNeUz43Cp2zGNEVX6=0RSaksA9zyJw@mail.gmail.com>
 <449d14b3cb80e259bbd9d8728ea204b15b6a025a.camel@j-davis.com>
 <CAHgHdKtb2jD+DaTJU+3jnQRZ9hEXSDcPCR8DCCzZTTVeo4jQcA@mail.gmail.com>
 <0c21d77497c2316f9f5af143122dd24a81eb40db.camel@j-davis.com>
In-Reply-To: <0c21d77497c2316f9f5af143122dd24a81eb40db.camel@j-davis.com>
From: Mark Dilger <mark.dilger@enterprisedb.com>
Date: Wed, 25 Mar 2026 17:01:26 -0700
Message-ID: 
 <CAHgHdKuGR7aJxZu7VTPA+kEDkzqJvKmi5799rhW+sKyt-WVihQ@mail.gmail.com>
Subject: Re: Use CASEFOLD() internally rather than LOWER()
To: Jeff Davis <pgsql@j-davis.com>
Cc: Daniel Verite <daniel@manitou-mail.org>, pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009da911064de216dc"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHgHdKuGR7aJxZu7VTPA%2BkEDkzqJvKmi5799rhW%2BsKyt-WVihQ%40mail.gmail.com>
Precedence: bulk

--0000000000009da911064de216dc
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Mar 25, 2026 at 2:02=E2=80=AFPM Jeff Davis <pgsql@j-davis.com> wrot=
e:

> I think the precise question would be: "are there any two characters
> that lowercase to the same character but do not casefold to the same
> character?".
>

I don't know.  I'll set up a test to iterate across all locales across all
character pairs... no, I didn't find any on my system.  Other searching
suggests that the Turkish and Azerbaijani locale do have this
characteristic, with I (U+0049) lowercasing to =C4=B1 (U+0131) and case fol=
ding
to i (U+0069) while =C4=B1 (U+0131) lowercases to =C4=B1 (U+0131) but also =
case folds
to =C4=B1 (U+0131).  I have not confirmed that empirically, though.


> I don't have a counterexample, so perhaps using casefold would still be
> fine.
>
> Thoughts? Should we enhance regexes to consider more than two case
> variants first, or should we proceed with some of these patches (and/or
> a similar change to pg_trgm)?
>

I don't want to take a strong position either way.  I'm still wrapping my
head around the various implications of the proposed changes, and don't
feel I have a complete picture yet.

--=20

*Mark Dilger*

--0000000000009da911064de216dc
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Mar 25,=
 2026 at 2:02=E2=80=AFPM Jeff Davis &lt;<a href=3D"mailto:pgsql@j-davis.com=
">pgsql@j-davis.com</a>&gt; wrote:</div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:so=
lid;border-left-color:rgb(204,204,204);padding-left:1ex">
I think the precise question would be: &quot;are there any two characters<b=
r>
that lowercase to the same character but do not casefold to the same<br>
character?&quot;.<br></blockquote><div><br></div><div>I don&#39;t know.=C2=
=A0 I&#39;ll set up a test to iterate across all locales across all charact=
er pairs... no, I didn&#39;t find any on my system.=C2=A0 Other searching s=
uggests that the Turkish and Azerbaijani locale do have this characteristic=
, with I (U+0049) lowercasing to =C4=B1 (U+0131) and case folding to i (U+0=
069) while =C4=B1 (U+0131) lowercases to =C4=B1 (U+0131) but also case fold=
s to =C4=B1 (U+0131).=C2=A0 I have not confirmed that empirically, though.<=
/div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px=
 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-co=
lor:rgb(204,204,204);padding-left:1ex">
I don&#39;t have a counterexample, so perhaps using casefold would still be=
<br>
fine.<br>
<br>
Thoughts? Should we enhance regexes to consider more than two case<br>
variants first, or should we proceed with some of these patches (and/or<br>
a similar change to pg_trgm)?<br></blockquote><div><br></div><div>I don&#39=
;t want to take a strong position either way.=C2=A0 I&#39;m still wrapping =
my head around the various implications of the proposed changes, and don=
9;t feel I have a complete picture yet.</div></div><div><br></div><span cla=
ss=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail=
_signature"><div dir=3D"ltr"><img src=3D"https://lh7-us.googleusercontent.c=
om/80Q0n8A18y4ur9BMmN9zDAoEOCSfqCbmGOQ2lI1IqwUyarDBFhlxOOMrLytOC5x5Zo8-TD9N=
OpLXzn1w2srXBDsTderYUy9-mH4Gb1KBetRMWRtZ2Tlsbk7YUDupvd1b1zTB3hZ2AQdJ5rZJ_AK=
jBPs" width=3D"220" height=3D"47.07423580786026" style=3D"color: rgb(0, 0, =
0); margin-left: 0px;"><br style=3D"color:rgb(0,0,0);font-family:Helvetica;=
font-size:18px"><div><font size=3D"4" face=3D"garamond, times new roman, se=
rif"><b>Mark Dilger</b></font></div></div></div></div>

--0000000000009da911064de216dc--