Postgress repository-how to create db with collation case insensitive?

[roman104]

Hi,
I am force to use Postgress as DBMS repository. I am using MySQL for many years. I would like to use search with case insensitive setting.
Do You have experience how to create DB in Postgress with collation case insensite, similar like in MS SQL?
Thank You
Roman

[qwerty]

In case you don't get an answer here you could ask that on StackOverflow...

q.

[Guillaume]

Hi,

Reopening this topic, in my case Postgres case sensitivity impacts EA searches, which is an issue for a client.

Using Postgres 12 , we created a case insensitive collation:
CREATE COLLATION case_insensitive (
 provider = icu,
 locale =
'@colStrength=secondary',
 deterministic = false

I then updated the SQL script to create the tables e.g. object_type varchar(255) COLLATE "case_insensitive",

Running a query with the equal operator works ok e.g. name = 'class' returns Class, CLASS and class. However queries using the "like" operator generates the following error:
nondeterministic collations are not supported for like

Looking at Stackoverflow posts, I don't see any solution.