How Can I Achieve Accent-Insensitive String Comparisons in PostgreSQL?

Does PostgreSQL support the "ignore accents" collation?

PostgreSQL itself does not natively support the "ignore accents" collation like Microsoft SQL Server does. However, two alternatives are available:

Use the Unaccent module

The Postgres community created the unaccent module, which provides functions for removing accents from strings. This can be used to create custom "ignore accents" collations.

<code class="language-sql">CREATE EXTENSION unaccent;
CREATE COLLATION ignore_accent (locale='en_US', provider='unaccent');</code>

Use ICU collation (PostgreSQL 12 or higher)

PostgreSQL 12 introduces support for ICU collations, which makes case-insensitive and accent-insensitive combinations and sorting possible. However, ICU collations have a performance penalty and cannot be used with certain operations such as pattern matching.

<code class="language-sql">CREATE COLLATION ignore_accent (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);</code>

Best Practices

For general use, the unaccent module is more efficient and recommended for creating "ignore accents" queries. It allows function inlining and expression indexing.

The above is the detailed content of How Can I Achieve Accent-Insensitive String Comparisons in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!