Clever Geek Handbook

How to make postgresql search method case insensitive?

I just switched from sqlite3 to postgresql-9.4. Earlier when I searched for my "typeahead" method, I got results that were not case sensitive. But now that I've switched, the method is suddenly case sensitive. How can I make a method case insensitive with postgresql?

def typeahead
  q = params[:query]
  render json: Subject.where('name like ? OR second_name like ? OR keywords like ?', "%#{q}%", "%#{q}%", "%#{q}%")
end
+3


source to share


3 answers


I think you can use ILIKE instead of LIKE

 Subject.where('name ilike ? OR second_name ilike ? OR keywords ilike ?', "%#{q}%", "%#{q}%", "%#{q}%")


or simplify a little

 Subject.where('name ilike :name OR second_name ilike :name OR keywords ilike :name', name: "%#{q}%")
+6


source


You can also use the "ilike" keyword instead of "like".



+4


source


The general way is as follows:

Subject.where('LOWER(name) like ?, "%#{q.downcase}%")

Note that you may need to create a lowercase index if you run into performance issues. See: How to create an index in PostgreSQL in lowercase only?

+2


source






More articles:


All Articles