#12 ✓resolved
Marcel Ruegenberg

Case-insensitivity in Postgres

Reported by Marcel Ruegenberg | October 20th, 2008 @ 07:13 AM

In PostgreSQL, the LIKE statement performs a case-sensitive search, while ILIKE performs a case-insensitive search. It is often (probably in the default case) desirable to perform a case-insensitive search.

Case-insensitivity should be the default case, i.e. searching with Postgres is performed using ILIKE instead of LIKE. Additionally, there could be an option to make searching case-sensitive (which is possible in both Postgres (by going back to using LIKE) and MySQL (see http://dev.mysql.com/doc/refman/...>

Comments and changes to this ticket

  • Ben Johnson

    Ben Johnson October 20th, 2008 @ 10:15 PM

    • State changed from “new” to “resolved”

    I had a recent email from someone using postgres as well. This is not a problem with searchgasm, but a bug in postgresql. He told me he found a patch, but I am not sure where he was looking.

    I bet if you do

    User.all(:conditions => ["name like ?", "%TEST%"])

    you wont get results either, and that line has nothing to do with searchgasm. In mysql, sqlite, etc you will get case insensitive results.

    There is a work around, but its nasty:

    s = User.new_search
    s.conditions.lower_of_name = params[:search][:conditions][:name].downcase

    Obviously not a good solution. Do some searches on postgresql being case sensative and I bet you'll find the solution. Let me know if you have any other issues.

  • Marcel Ruegenberg

    Marcel Ruegenberg October 21st, 2008 @ 05:06 AM

    Well, it's not exactly a bug apparently, but rather the SQL standard not being sufficiently detailed. Apparently it doesn't really specify whether LIKE should be case-sensitive or not (see http://archives.postgresql.org/p... and Postgres decided to do it case-sensitive while MySQL, SQLite etc. decided on case-insensitivity.

    What does work for me is

    User.all(:conditions => ["name ilike ?", "%TEST%"])

    (note the i before like). (The documentation describing the ILIKE statement is at http://www.postgresql.org/docs/8... )

    Maybe this could be incorporated in the Postgres connection adapter somehow.

    For now, I was planning to write my own condition for ILIKE and register it, as described in Searchgasm::Conditions::Base.register_condition, but the code there doesn't work, because Searchgasm::Search::Conditions doesn't have a method register_condition. Even if I use Searchgasm::Conditions::Base which does have the method, @search.conditions.name_ilike with my new condition doesn't work.

    (There is also a small typo in the documentation there: Searchgasm::Seearch::Conditions instead of Searchgasm::Search::Conditions)

  • Ben Johnson

    Ben Johnson October 21st, 2008 @ 10:40 AM

    I agree, adding the ilike condition makes the most sense, because that is a condition just like "like" according to postgresql. I'll add it in for you. Update from the repo to get the new condition.

  • Georg Ledermann

    Georg Ledermann October 21st, 2008 @ 03:14 PM

    • Assigned user set to “Ben Johnson”

    Hm, I don't think that adding conditions like "ilike" is the right way, because writing database agnostic applications is more difficult with such a thing. Indeed, I see searchgasm as a very great tool to write Rails application more independent from the database layer.

    What about adding conditions named "contains_case_sensitive" and "contains_case_insensitive" (you will find better words), which are mapped to the corresponding syntax depending of the active database connector?

  • Ben Johnson

    Ben Johnson October 21st, 2008 @ 03:17 PM

    I was thinking about that too, because if you wanted to switch from postgres to mysql you would have to change ilike all over the place.

    I think I am just going to use ilike if it is a postgres connection in the like condition.

    Good call on that georg.

  • Ben Johnson

    Ben Johnson October 21st, 2008 @ 04:20 PM

    I removed this and the like and keywords conditions should work properly and use ilike for postgres connections.

  • Georg Ledermann
  • Marcel Ruegenberg

    Marcel Ruegenberg November 21st, 2008 @ 05:00 AM

    • Tag changed from case, ilike, like, postges, sensitive to begins, case, condition, ilike, like, postgres, sensitive

    begins_with and ends_with probably should be case-insensitive, too...

  • Kacper Bielecki

    Kacper Bielecki July 23rd, 2009 @ 04:01 AM

    It looks like this problem came back in second searchlogic version.

    I attach a patch which fixes it for my adapter.

    Patch adds also possibility of adding additional parameters to generated link by order helper method...

  • Kacper Bielecki

    Kacper Bielecki July 28th, 2009 @ 08:24 AM

    Already added pull request with these changes....

  • edpl

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Provides common named scopes and object based searching.


Referenced by