Menu
By default, PostgreSQL comes without case insensitive collations. Its documentation describes a way to add new collations based on OS locales, e.g.:
http://www.postgresql.org/docs/9.1/static/sql-createcollation.html
however the created collations are case sensitive.
My question is: How to add a case INsensitive collation to a PostgreSQL server ?
Can somebody describe a workflow ?
I was not able to find any references on the net, while some people mention that it is possible. I've tested with v9.1 - Debian GNU/Linux and v9.2 on Windows.
Just to clarify that I'm aware of:
These are not the solutions that I want.
471
While using regular expressions, we need to use the PostgreSQL ~* operator instead of the like operator; we can also use the ilike operator in PostgreSQL. We can also create an extension name as citext to use the case insensitive query in PostgreSQL; we need to create it first to use the extension of citext.
A case-insensitive collation ignores the differences between uppercase and lowercase letters for string comparison and sorting, whereas a case-sensitive collation does not. For example, in case-insensitive collation, “A” and “a” are equal.
No, these both are the same, just a different naming convention. As a_horse_with_no_name said, Postgres uses the collation implementation from the OS. There is no way to get the same result on both operating systems. In your case you may(I said maybe)do like this: ORDER BY lower(fieldname) .
String comparisons in PostgreSQL are case sensitive* (unless a case-insensitive collation were to be introduced). To work around this, PostgreSQL has several methods to match strings in a case-insensitive manner. Consequently, Marten also makes use of these methods to translate case-insensitive queries to pgSQL.
"Nondeterministic collations" were added to Version 12:
"The most typical use case for nondeterministic collations is probably the case-insensitive comparison. At secondary strength, strings that differ by case compare as equal"
166
Doesn't seem to be possible within Postgres iteself:
Not from the Postgres project -- we just use the collations supplied by the operating system.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
