On Thu, 19 Jun 2025 at 18:39, David E. Wheeler <da...@justatheory.com> wrote:
>
> On Jun 19, 2025, at 12:59, Thom Brown <t...@linux.com> wrote:
>
> > No. But given the options, I would personally choose nondeterministic 
> > collations now that they are available. I just wish they were more 
> > user-friendly as I suspect the majority of people either won't know about 
> > them, or won'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 unclear 
> on the migration path from one to the other, let alone implications for any 
> infrastructure they built around cutest (like function signatures and return 
> values). As long as citext conteinues to be maintained there 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


Reply via email to