Sample Header Ad - 728x90

Does PostgreSQL support ICU collation's options and settings?

3 votes
1 answer
3516 views
ICU Specifies different [LDML Collation Settings](http://www.unicode.org/reports/tr35/tr35-collation.html#Setting_Options) . Some of them seem pretty interesting, especially the ones on case and accent, > * “Ignore accents”: strength=primary > * “Ignore accents” but take case into account: strength=primary caseLevel=on > * “Ignore case”: strength=secondary > * “Ignore punctuation” (completely): strength=tertiary alternate=shifted > * “Ignore punctuation” but distinguish among punctuation marks: strength=quaternary alternate=shifted potentially a better method of doing what You can also see these [documented here](http://userguide.icu-project.org/collation/customization) . Are these ICU options and settings possible with [PostgreSQL 10 ICU collation support](https://www.postgresql.org/docs/10/static/collation.html) ? CREATE COLLATION special (provider = icu, locale = 'en@strength=primary'); SELECT 'Å' LIKE 'A' COLLATE "special"; # returns false I've also tried the [CLDR BCP47](http://cldr.unicode.org/index/bcp47-extension) > Starting with ICU 54, collation attributes can be specified via locale keywords as well, in the old locale extension syntax ("el@colCaseFirst=upper") or in language tag syntax ("el-u-kf-upper"). Keywords and values are case-insensitive. See the [LDML Collation spec, Collation Settings](http://www.unicode.org/reports/tr35/tr35-collation.html#Collation_Settings) , and the [data file](http://unicode.org/repos/cldr/trunk/common/bcp47/collation.xml) listing the valid collation keywords and their values. (The deprecated attributes kh/colHiraganaQuaternary and vt/variableTop are not supported.) For that, this looked right CREATE COLLATION special (provider = icu, locale = 'en-ks-level1'); SELECT 'Å' LIKE 'A' COLLATE "special"; # returns false Also tried en-u-ks-level1 That method seems to be what the docs go for, CREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');
Asked by Evan Carroll (65502 rep)
Nov 28, 2017, 04:18 PM
Last activity: Oct 4, 2019, 12:21 PM