Add case insensitive collation to PostgreSQL

Question

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:

PostgreSQL CITEXT add-on data type
Usage of LOWER/UPPER for searches and indexes.

These are not the solutions that I want.

Can you give reasons for the solutions you don't want. Maybe there is another way you haven't thought of? I can guess at problems with lower/upper, but citext avoids these: as with a custom collation, it adjusts the meaning of = so that the case-insensitivity is transparent to calling code. — IMSoP
– IMSoP, Commented Sep 15, 2013 at 22:24
I could not agree more with your question. Need the same thing myself. Did you solve it? If yes, please add your own answer. — mark
– mark, Commented Nov 11, 2013 at 11:24

Doug Winsby · Accepted Answer · 2019-11-26 02:58:16Z

4

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

answered Nov 26, 2019 at 2:58

Doug Winsby

412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DrColossos · Accepted Answer · 2013-07-02 10:18:54Z

3

Doesn't seem to be possible within Postgres iteself:

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

answered Jul 2, 2013 at 10:18

DrColossos

13.1k3 gold badges49 silver badges68 bronze badges

1 Comment

Delian Krustev Over a year ago

I don't care where the collation definition will come from. It might be from the OS, downloaded from the net, etc .. I just want them to be available to the DB.

Collectives™ on Stack Overflow

Add case insensitive collation to PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related