Re: Add CASEFOLD() function.

On Thu, 19 Jun 2025 at 18:39, David E. Wheeler <david@justatheory.com> wrote:
>
> On Jun 19, 2025, at 12:59, Thom Brown <thom@linux.com> wrote:
>
> > No. But given the options, I would personally choose nondeterministic collations now that they are available. I
justwish they were more user-friendly as I suspect the majority of people either won't know about them, or won't know
howto use them. 
>
> I suspect there are a lot of uses of citext for databases created before nondeterministic collations existed and
peopleare unaware of them or unclear on the migration path from one to the other, let alone implications for any
infrastructurethey built around cutest (like function signatures and return values). As long as citext conteinues to be
maintainedthere and there’s no super clear path to migrate, I’d 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