Message-ID: <ab9a2b8d25ff826cb785a1d0f21391538ce2aff6.camel@j-davis.com>
Subject: Re: Add CASEFOLD() function.
From: Jeff Davis <pgsql@j-davis.com>
To: Thom Brown <thom@linux.com>, Peter Eisentraut <peter@eisentraut.org>
Cc: Vik Fearing <vik@postgresfriends.org>, Joe Conway <mail@joeconway.com>,
 Ian Lawrence Barwick
	 <barwick@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: Thu, 19 Jun 2025 09:33:41 -0700
In-Reply-To: 
 <CAA-aLv57dEeoX__Osx4k2yda6G2V-i=MYxr0HrH6U+yyYw5wKQ@mail.gmail.com>
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.3-0ubuntu1 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/ab9a2b8d25ff826cb785a1d0f21391538ce2aff6.camel%40j-davis.com>
Precedence: bulk

On Thu, 2025-06-19 at 16:36 +0100, Thom Brown wrote:
> Ease of use, perhaps. It seems easier to use:
>=20
> column_name cftext
>=20
> rather than:
>=20
> CREATE COLLATION case_insensitive_collation (
> =C2=A0=C2=A0=C2=A0 PROVIDER =3D icu,
> =C2=A0=C2=A0=C2=A0 LOCALE =3D 'und-u-ks-level2',
> =C2=A0=C2=A0=C2=A0 DETERMINISTIC =3D FALSE
> );

We could auto-create such a collation at initdb time for ICU-enabled
builds.

> 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.

There are at least three ways to do case insensitivity (or other kinds
of equivalence):

* Explicit function calls in queries, as well as index and constraint
definitions. E.g. expression index on LOWER(), queries that explicitly
do "LOWER(x) =3D ..."

* Wrap those function calls up in a separate data type, like citext.

* Non-deterministic collations.

Given that we have collations, which are a way of organizing alternate
behaviors for existing data types, I'm not sure I see the need for
creating an entirely separate data type.

> I guess I don't feel strongly about it either
> way.

Are you a user of citext? I'm genuinely interested in the use cases,
and whether the separate-data-type approach has merits that are missing
in the other approaches.

Regards,
	Jeff Davis