5

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

2

2 Answers 2

6

This may be too late for the original poster, but for completeness, the way to achieve case insensitive behaviour from PostgreSQL is to set a non-deterministic collation. This is only for Postgres 12.

Details are described in docs here. Reproducing relevant portion for completeness:

A collation is either deterministic or nondeterministic. A deterministic collation uses deterministic comparisons, which means that it considers strings to be equal only if they consist of the same byte sequence. Nondeterministic comparison may determine strings to be equal even if they consist of different bytes. Typical situations include case-insensitive comparison, accent-insensitive comparison, as well as comparison of strings in different Unicode normal forms. It is up to the collation provider to actually implement such insensitive comparisons; the deterministic flag only determines whether ties are to be broken using bytewise comparison. See also Unicode Technical Standard 10 for more information on the terminology.

To create a nondeterministic collation, specify the property deterministic = false to CREATE COLLATION, for example:

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);

This example would use the standard Unicode collation in a nondeterministic way. In particular, this would allow strings in different normal forms to be compared correctly. More interesting examples make use of the ICU customization facilities explained above. For example:

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);

All standard and predefined collations are deterministic, all user-defined collations are deterministic by default. While nondeterministic collations give a more “correct” behavior, especially when considering the full power of Unicode and its many special cases, they also have some drawbacks. Foremost, their use leads to a performance penalty. Also, certain operations are not possible with nondeterministic collations, such as pattern matching operations. Therefore, they should be used only in cases where they are specifically wanted.

2
  • Thanks for this answer. From what I'm reading, case-insensitivity requires a non-deterministic collation, and LIKE is not supported for non-deterministic collations. Are you saying that there is no way in Postgres to search a column for a substring while ignoring case? So, for example, I can't search a column containing URLs for all URLs which start [HTTPS|https|Https] except by building a query that specifies every combination of case? That seems like a massive limitation for Postgres.
    – Peter
    Aug 27, 2021 at 9:44
  • 1
    @Peter, you can, although with some tradeoffs (i.e. a performance hit depending on the amount of data in your table). Your options are to use LOWER(columnName) = value OR ILIKE Aug 30, 2021 at 23:43
1

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?

1
  • 2
    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, 2015 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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