Re: Upgrading locale issues

rihad wrote:

> Thanks for the reply. Do you know what would a "decent" ICU collation be
> to bind to a field's schema definition so it would mimic a UTF-8
> encoding for a multilingual column? Maybe und-x-icu? We aren't as much
> concerned about their sortability in most cases, we just want indexes to
> better handle future PG/ICU upgrades. But what does und(efined) even
> mean with respect to collations?

"undefined" in this context means unspecified language and
unspecified country or region. It implies that no language-specific
nor regional rule will be applied to compare strings.

Using C.UTF-8 as the collation for text fields to index may be the
best trade-off in your case. It should be immune to libc and ICU
upgrades.

With C.UTF-8, a string like 'BC' will sort before 'ab', and punctuation
and accents will also sort differently than with a linguistic-aware
collation.
If your applications care about that, it can be fixed by simply
adding COLLATE "default" to the ORDER BY clause of the queries that
are meant to present data to users.
COLLATE "default" means the collation of the database, which
presumably would be something like "language_REGION.UTF-8" in your
case. If you never specified it explicitly, it came from initdb which
itself got it from the environment of the server.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite