Collation for accent-insensitive comparison on Postgres?

Question

On PG 13 documentation, there are several examples of ICU collations for specialized purposes. It is also mentioned that ICU locales exist that allow creating collations to ignore accents, and that they can be found on https://github.com/unicode-org/cldr/blob/master/common/bcp47/collation.xml

However, after reading that document, I am still unclear on what is the locale I should use to create an ICU collation for accent insensitive comparisons in Spanish.

What is the name of such an ICU locale? Is there any list of ICU Spanish collations?

score 3 · Accepted Answer · answered Sep 13 '21 at 03:05

This would be a case sensitice, but accent insensitive locale for Spain that works with recent versions of ICU:

CREATE COLLATION spanish_noaccent (
   PROVIDER = 'icu',
   LOCALE = 'es-ES-u-ks-level1-kc',
   DETERMINISTIC = FALSE
);
SELECT 'Olé' = 'Ole' COLLATE spanish_noaccent;
?column? 
══════════
 t
(1 row)
SELECT 'Olé' = 'ole' COLLATE spanish_noaccent;
?column? 
══════════
 f
(1 row)

ks-level1 means "case and accent insensitive", and kc adds case sensitivity.

See my blog post for more.

Collation for accent-insensitive comparison on Postgres?

1 Answers1