MIME-Version: 1.0
References: <a1886ddfcd8f60cb3e905c93009b646b4cfb74c5.camel@j-davis.com>
 <CAB8KJ=jgnpbDTJS+jOQMzo7b1mPTwM0hCqQBBRgoX_kvyxWVog@mail.gmail.com>
 <ab1bdabcf64d799692ddaa5b06b5ef8afe94a0ef.camel@j-davis.com>
 <47716a78-eb0b-4728-8991-fc0d958a01c9@joeconway.com>
 <c3801da038f8f59ee63deb9104af1147d49454c8.camel@j-davis.com>
 <36f69fa9-db3f-44a1-8c8a-95ff09704a9c@postgresfriends.org>
 <af6b575335ffeea36db3189fe7031adf67c250d2.camel@j-davis.com>
 <692d28a2-d5f8-4db5-a3ad-c7db9bab522f@postgresfriends.org>
 <e26ae6f202aff57a76e14cfb8ba2202550257f27.camel@j-davis.com>
 <CAA-aLv7KLoT9yCdiJwRP9PeL_4yNTzQ3T8WJLbTtX=Ld45UOpg@mail.gmail.com>
 <d230fd82-a285-4768-9b17-9b57ac2ff2a2@eisentraut.org>
 <CAA-aLv57dEeoX__Osx4k2yda6G2V-i=MYxr0HrH6U+yyYw5wKQ@mail.gmail.com>
In-Reply-To: 
 <CAA-aLv57dEeoX__Osx4k2yda6G2V-i=MYxr0HrH6U+yyYw5wKQ@mail.gmail.com>
From: Robert Treat <rob@xzilla.net>
Date: Thu, 19 Jun 2025 12:15:48 -0400
Message-ID: 
 <CABV9wwM36FAN38jURxw-U5u-FPcbtbcnZGDT6ip_fRRaRFZzZg@mail.gmail.com>
Subject: Re: Add CASEFOLD() function.
To: Thom Brown <thom@linux.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, Jeff Davis <pgsql@j-davis.com>,
	Vik Fearing <vik@postgresfriends.org>, Joe Conway <mail@joeconway.com>,
	Ian Lawrence Barwick <barwick@gmail.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CABV9wwM36FAN38jURxw-U5u-FPcbtbcnZGDT6ip_fRRaRFZzZg%40mail.gmail.com>
Precedence: bulk

On Thu, Jun 19, 2025 at 11:37=E2=80=AFAM Thom Brown <thom@linux.com> wrote:
> On Thu, 19 Jun 2025 at 15:51, Peter Eisentraut <peter@eisentraut.org> wro=
te:
> > On 19.06.25 06:03, Thom Brown wrote:
> > > Late to the party, but is there an argument for porting this to the
> > > citext type? Or supplementing the extension with an additional type
> > > ("cftext"? *shrug*). It currently uses lower(), so our current
> > > recommendation for dealing with all unicode characters is to use
> > > nondeterministic collations.
> >
> > What is the motivation for wanting a citext variant instead of using
> > nondeterministic collations?
>
> Ease of use, perhaps. It seems easier to use:
>
> column_name cftext
>
> rather than:
>
> CREATE COLLATION case_insensitive_collation (
>     PROVIDER =3D icu,
>     LOCALE =3D 'und-u-ks-level2',
>     DETERMINISTIC =3D FALSE
> );
>
> column_name text COLLATE case_insensitive_collation
>
> But I see the arguments against it. It creates an unnecessary
> dependency on an extension, and if someone wants to ignore both case
> and accents, they may resort to using 2 extensions (citext + unaccent)
> when none are needed. I guess I don't feel strongly about it either
> way.

Don't forget, if you have a defined insensitive / normalized
collations, you also enable on-the-fly collation based matching, a la
"SELECT '=C3=85' =3D 'A' COLLATE ignore_accent_case;" regardless of the
provided collations (which I think is much more common certain in
other databases)

Robert Treat
https://xzilla.net