Skip to main content
Share Your Experience: Take the 2024 Developer Survey
10 votes
Accepted

PostgreSQL nondeterministic collations are not supported for LIKE

Assuming that most of the time the custom, non-deterministic collation works for you and you only need something deterministic occasionally, then you can simply provide the deterministic collation ...
Solomon Rutzky's user avatar
10 votes

What is the meaning of "-x-icu" in PostgreSQL's "collates"?

The documentation, 23.2. Collation Support / 23.2.2.2.2. ICU Collations , states: Collations provided by ICU are created in the SQL environment with names in BCP 47 language tag format, with a “...
Solomon Rutzky's user avatar
8 votes
Accepted

Does PostgreSQL support ICU collation's options and settings?

Case insensitive or accent-insensitive collations cannot be used prior to PostgreSQL 12, because internally PostgreSQL considers that strings with a different binary representation are not equal. When ...
Daniel Vérité's user avatar
4 votes

Specify ICU collations as `ENCODING`, `LC_COLLATE`, and `LC_CTYPE` in Postgres 10

First let's note that at the time of this answer, PostgreSQL 10 in still in beta stage. Some issues about the ICU integration and how it's documented are still under discussion, and there might be ...
Daniel Vérité's user avatar
3 votes
Accepted

Collation for accent-insensitive comparison on Postgres?

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-...
Laurenz Albe's user avatar
  • 52.8k
3 votes

Equivalent of utf8_general_ci in Postgres/ICU?

The dotless I is a special case. It's processed by the ICU collation service with rules that depend on the language. If the locale refered to the Turkish or Azerbaijani languages, it would produce the ...
Daniel Vérité's user avatar
2 votes
Accepted

Was my Postgres cluster built with the ICU libraries available for Postgres 10 and later?

Does this mean I have the ICU libraries installed, or not? The configure option that really brings in the library is --with-icu. Currently, checking for this one seems the more reliable way to tell ...
Daniel Vérité's user avatar
2 votes

Numeric collation sorts by digits instead of value- postgres

This is a potential alternative solution to your problem. Rather than creating a collation, you could do following (all of the code below is available on the fiddle here): CREATE TABLE test ( e_id ...
Vérace's user avatar
  • 30k
2 votes
Accepted

Identify the version of a collation from ICU in Postgres

TL;DR Check out the pg_collation_actual_version() function.   DETAILS The PostgreSQL documentation for ALTER COLLATION seems to discuss this issue in the "Notes" section: When using ...
Solomon Rutzky's user avatar
1 vote
Accepted

Postgres sorting order not respecting default C collation?

C.UTF8 is not a valid ICU collation. If you give ICU a garbage collation name like that, it ignores what it cannot parse, and you probably end up with the und collation instead. That would explain ...
Laurenz Albe's user avatar
  • 52.8k
1 vote

Numeric collation sorts by digits instead of value- postgres

Since you say that you are using windows, the problem could be that you are using an old version of the ICU library that does not yet understand the syntax you are using. Try CREATE COLLATION numeric (...
Laurenz Albe's user avatar
  • 52.8k
1 vote

Identify the version of a collation from ICU in Postgres

To compare collation-versions of a Postgresql-16 DB-cluster with respective OS collation versions select oid, collname, collversion "orig_version" , pg_collation_actual_version(oid) "curr_version" ...
user290482's user avatar
1 vote

International Components for Unicode (ICU) Date Formatter for MySQL?

I think the answer is... Use MySQL to store dates/datetimes/timestamps in the limited formats that it provides, then use app code -- possibly some library in your app language -- to get at fancier ...
Rick James's user avatar
  • 78.3k

Only top scored, non community-wiki answers of a minimum length are eligible