Generic Ruby solution for SQLite3 "LIKE" or PostgreSQL "ILIKE"?
pattern-matching, postgresql, ruby-on-rails, search, sqlite
Solution
The root of the problem lies here:
I am using SQLite3 for development and PostgreSQL for deployment.
That's a bad idea™. You will keep running into incompatibilities - or worse: not realize some until damage is done. Use the same RDBMS (PostgreSQL) for development and production and save yourself the pointless trouble.
While you are stuck with your unfortunate setup, there is a simple fix:
lower(style) LIKE lower(?)
Works on both platforms alike.
Drop the right-hand `lower()` if you provide a lower-case search-pattern.
In standard SQLite `lower(X)` only folds ASCII letters. Quoting the chapter Core Functions in the SQLite manual:
The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension.
Bold emphasis mine.
PostgreSQL `lower(X)` works with UTF-8 out of the box.
As a welcome side effect, you can speed up that query in PostgreSQL with an index on the expression `lower(style)`, which will be faster than using `ILIKE` and a basic index on `style`.
Also, since PostgreSQL 9.1 you can use a GIN or GIST index with the `pg_trgm` extension to speed up any `LIKE` and `ILIKE` query - trigram indexes are case-insensitive. Details:
- Similar UTF-8 strings for autocomplete field
Problem
I am using SQLite3 for development and PostgreSQL for deployment. However, I am facing the following problem: My simple search using `SQLite3`: ``` def self.search(search) if search find(:all, :conditions => ["style LIKE ? OR construction LIKE ?", "%#{search}%", "%#{search}%"]) else find(:all) end end ``` However, it doesn't work for `PostgreSQL`, and I need to replace the `LIKE` for `ILIKE` to solve the problem: ``` def self.search(search) if search find(:all, :conditions => ["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"]) else find(:all) end end ``` Is there a "Ruby way" to do these searches across any database? EDIT - based on your answers I don't believe I will find a generic Ruby solution for that. I have followed the Ruby on Rails Tutorial: Learn Rails by Example - by Michael Hartl, where the final Gemfile shows both databases... well, disappointing...