CODEWITHSUNDEEP
databasepostgresqlcollation
Delian Krustev
Delian Krustev

Reputation: 2906

Add case insensitive collation to PostgreSQL

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.

Upvotes: 18

Views: 8361

Answers (2)

Doug Winsby
Doug Winsby

Reputation: 41

"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"

Upvotes: 4

DrColossos
DrColossos

Reputation: 12998

Doesn't seem to be possible within Postgres iteself:

Not from the Postgres project -- we just use the collations supplied by the operating system.

Upvotes: 3

Related Questions