Re: Built-in case-insensitive collation pg_unicode_ci

On Fri, 2025-09-19 at 17:21 -0700, Jeff Davis wrote:

> ----------
> Versioning
> ----------
>
> Unlike other built-in collations, the order does depend on the
> version
> of Unicode...
> That means that indexes, including primary keys, can become
> inconsistent after a major version upgrade...

There's another option here: we can have the PG_UNICODE_CI collation
throw an error when the comparison involves unassigned code points.
That would give us assurance that primary keys remain consistent across
upgrades.

While not every user would want that for their entire database, I think
it's a good idea in the case of PG_UNICODE_CI:

* It would ensure that all primary keys using any builtin collation
are stable across upgrades.
* If the data is somewhere else, like an unindexed column or an index
with a different collation, then unassigned code points would still be
just fine.
* The cases where you'd want to use the PG_UNICODE_CI collation are
also the cases where it's not so important to permit very-recently-
assigned code points.
* Applications already need to expect errors when inserting into a
primary key or unique index, so it wouldn't require rewriting
applications to handle such errors.

Regards,
Jeff Davis