Re: Built-in case-insensitive collation pg_unicode_ci

On 20.09.25 02:21, Jeff Davis wrote:
> New builtin case-insensitive collation PG_UNICODE_CI, where the
> ordering semantics are just:
>
> strcmp(CASEFOLD(arg1), CASEFOLD(arg2))
>
> and the character semantics are the same as PG_UNICODE_FAST.

If it's a variant of PG_UNICODE_FAST, then it ought to be called
PG_UNICODE_FAST_CI or similar. Otherwise, one would expect it to be a
variant of PG_UNICODE (if that existed, but there is also UNICODE).

But that name is also dubious since you later write that it's not
actually fast.

> Non-deterministic collations cannot be used by SIMILAR TO, and may
> cause problems for ILIKE and regexes. The reason is that pattern
> matching often depends on the character-by-character semantics, but ICU
> collations aren't constrained enough for these semantics to work.

This reasoning is a bit narrow. SIMILAR TO is kind of deprecated, and
ILIKE is kind of stupid, and regexes have their own way to control
case-sensitivity.

Nevertheless, I think there would be some value to provide CI (and maybe
accent-insensitive?) collations that operate separately from the
"nondeterministic" mechanism. But then I would like to see a
comprehensive approach that covers a variety of providers and locales.
For example, I would expect there to be something like a "sv_SE_CI"
locale, either available by default or easily created.