current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I am using the postgres version 9.4.1 64-bit on windows 7 64-bit. I need to create DB with the setting "case sensitive = OFF" but couldn't make this work. I refereed already many question on SO and other forums as well but even after trying out all those options my DB is still case sensitive and my search queries returns only partial results.

Does any one have success getting this work on windows environment?

Note: I am aware of the ILIKE operator but that option is not going to be considered by Architect team as we have a clear requirements that Database storage should be CASE INSENSITIVE by default. We do ot have any scenarios where we will need case sensitive search.

enter image description here

enter image description here

share|improve this question
2  
There is no such option. But maybe you can use citext data type: postgresql.org/docs/current/static/citext.html –  a_horse_with_no_name May 12 at 14:56
    
I get this impression that it is possible using proper OS locale from one of the response in following question. chekc the answers from "Mike Sherrill 'Cat Recall' in this forum. "stackoverflow.com/questions/7005302/…; –  Anup Shah May 12 at 15:57

1 Answer 1

up vote 0 down vote

You should take a look at my recent answer here to a related question regarding umlauts.

Basically, the solution involves a "shadow" or "search" column for proper names. Basically, you use an ON INSERT trigger to populate your search column with the field you wish to search modified for your chosen search method - in this example, case insensitive.

The example I gave was using Irish names 

Display_Name    Search_name    Address
------------    -----------    -------
Ronan MacGuire  RONAN MCGUIRE  1 Clontarf D1
Ronan McGuire   RONAN MCGUIRE  2 Malahide D2
Rónán Mcguire   RONAN MCGUIRE  3 Mary's terrace D3

Ignore the bits about Mc/Mac, just concentrate on the last Display_Name "Rónán Mcguire" - in the Search_Name column, this is changed to RONAN MCGUIRE - and any search for MCGUIRE will also pick up the second person "Ronan McGuire". This has the advantage of being portable/RDBMS agnostic. Maybe this could be a solution to your problem?

share|improve this answer
    
It is a good solution if I needed this for any specific Table/Column. But but this is really impractical for me to do for all string columns in all tables. I need to propose something to Architect team that is supported by DB engine natively, for example like SQL server Does. –  Anup Shah May 12 at 15:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.