First things first, please forgive me if my question seems so naive (I am not a DBA). I look up to see how can I make PostgreSQL case-insensitive and I found the Nondeterministic Collations section of the official docs useful. I have tried the following but there was no luck.
CREATE COLLATION ci_fa (provider = icu, locale = 'fa-IR-x-icu', deterministic = false); -- also locale = 'fa-IR' and 'fa-IR.UTF8'
I found this answer but the locale used there, is not based on what I am looking for. Also, the example in the docs reads as:
CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
This works fine but I faced with a wired behavior in sorting. If I had a non-English character in my order column the result would be different. When I have
b and the non-English char, the result is
b. And when I have
B and the non-English char, the result is
On the other hand, reading a lot of articles and questions made me wondered about the following questions?
Having case-insensitive data impact the performance, so should I use it or not? Considering the following facts:
- 99% of my data has no casing. Our language is simpler so there is no Lower/Upper case in letters :)
- I use Full-Text indices for the search in the parts that users can input English words. I don't know how my collation will affect Full-Text.
- I don't like any of the suggested solutions here (How to make “case-insensitive” query).
- Although I use EF Core 5, I have DB functions to handle complex queries.
I am at the early stage of my project and for the first time, I decided to migrate to PostgreSQL from SQL Server. Should I use MySQL instead?
- I need Persian collation (Not sure if I do, the collation in the docs handles the sorting fine but I did not test storing it - in CentOs).
- I need Geolocation.
- I need Full-Text.
- And obviously, performance.
ahave equal weight, and the engine is within its rights to return them in any order, so I don't understand what you find surprising.
Ahave equal weight, so do
B. and I am surprised because I didn't expect changing the value of one row to affect others while they have the same weight. In the end, this is not my question as I understand both results are correct however it is not consistent.