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>
 <ab9a2b8d25ff826cb785a1d0f21391538ce2aff6.camel@j-davis.com>
 <CAA-aLv5Se9zt3CxcGWYwJUA-0nnx+sAArwUWRJKTwTL1a=8YyA@mail.gmail.com>
 <81AFE376-ACFE-4F78-A33B-9B5F2339FDA7@justatheory.com>
In-Reply-To: <81AFE376-ACFE-4F78-A33B-9B5F2339FDA7@justatheory.com>
From: Thom Brown <thom@linux.com>
Date: Thu, 19 Jun 2025 19:55:43 +0100
Message-ID: 
 <CAA-aLv5rW4p=8u0C1Od36LiLes2v3225uS6fYduORhTAW7w3mg@mail.gmail.com>
Subject: Re: Add CASEFOLD() function.
To: "David E. Wheeler" <david@justatheory.com>
Cc: Jeff Davis <pgsql@j-davis.com>, Peter Eisentraut <peter@eisentraut.org>,
	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/CAA-aLv5rW4p%3D8u0C1Od36LiLes2v3225uS6fYduORhTAW7w3mg%40mail.gmail.com>
Precedence: bulk

On Thu, 19 Jun 2025 at 18:39, David E. Wheeler <david@justatheory.com> wrot=
e:
>
> On Jun 19, 2025, at 12:59, Thom Brown <thom@linux.com> wrote:
>
> > No. But given the options, I would personally choose nondeterministic c=
ollations now that they are available. I just wish they were more user-frie=
ndly as I suspect the majority of people either won't know about them, or w=
on't know how to use them.
>
> I suspect there are a lot of uses of citext for databases created before =
nondeterministic collations existed and people are unaware of them or uncle=
ar on the migration path from one to the other, let alone implications for =
any infrastructure they built around cutest (like function signatures and r=
eturn values). As long as citext conteinues to be maintained there and ther=
e=E2=80=99s no super clear path to migrate, I=E2=80=99d bet good money that=
 few would bother to switch.

Maybe the citext doc page should explain how to get unhooked from it.
Something like:

ALTER TABLE mytable
  ALTER COLUMN ci_column
  SET DATA TYPE TEXT COLLATE case_insensitive_collation;

or

CREATE DOMAIN ci_text AS text
  COLLATE case_insensitive_collation;

ALTER TABLE mytable
  ALTER COLUMN ci_column
  SET DATA TYPE ci_text;

And because they're binary-compatible, they should also be free. No
doubt a procedure could do this to every instance in the database,
although I guess it gets trickier when it comes to functions that
accept citext as a parameter type, and other similar examples.

Thom