Rails SQL query with% Wildcards works in SQLite, but not PostgreSQL?

Question

I have a query that I use to search with these conditions:

:conditions => ['family_name LIKE ? OR given_name LIKE ?', "%#{params[:search]}%", "%#{params[:search]}%"] 

The query works fine locally on SQLite, but when I click on Heroku on PostgreSQL, only the first% works for both family_name and given_name. In other words, it will match a keyword that occurs at the end of a word, but not at the beginning or in the middle.

Example: There is an existing entry with: family_name => "Washington" and: given_name => "George"

A search for "ington" or "rge" will retrieve this entry. A search for “Wash” or “Geo” will not.

I am a new user and quite new to rails. Thanks in advance for your help.

---

Decision

Related topics: 1 2

Here's the fix I'm using:

 :conditions => ['LOWER(family_name) LIKE ? OR LOWER(given_name) LIKE ?', "%#{params[:search].downcase}%", "%#{params[:search].downcase}%"] 

Answer 1

LIKE is a case-sensitive operator in Postgres. Use ILIKE or other standard methods to match case- ILIKE patterns .