Icu provider for collation

Support
1

I use dotnet (entity framework) for creating/updating/managing the database
The sql script tries to create a collation

CREATE COLLATION "nonDeterministic" (
    LC_COLLATE = 'en-u-ks-primary',
    LC_CTYPE = 'en-u-ks-primary',
    PROVIDER = icu,
    DETERMINISTIC = False
);

but I get an error saying
ERROR: parameter "locale" must be specified (SQLSTATE 42P17)
Is this because of mising icu extension or some other feature that I am not aware of?

2

I’ve never used locales in PostgreSQL, but it seems that the behavior changed between PostgerSQL 14 and 15. Now if you use PROVIDER = icu, you should specify LOCALE = ..., not LC_COLLATE/LC_CTYPE(which are for PROVIDER = libc only). See Thread: CREATE COLLATION must be specified : Postgres Professional and Add option to use ICU as global locale provider · postgres/postgres@f2553d4 · GitHub

1 Like
3

I also tried

CREATE COLLATION some_collation (
    PROVIDER = libc,
    LOCALE = 'en-u-ks-primary',
    DETERMINISTIC = FALSE
);

but I get
ERROR: nondeterministic collations not supported with this provider (SQLSTATE 0A000)

I need non deterministic to be able to do case insensitive search
I know there are other option but collation would have been best case scenario

4

We compiled the PG14 and PG15 with ICU support. You can try it again.