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

Question

I have a query I'm using for a search with :conditions like this:

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

The query works fine locally on SQLite, but when I push to 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 the beginning or middle.

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

A search for "ington" or "rge" will retrieve this record. A search for "Wash" or "Geo" will not.

I'm a new user and am pretty new to rails. Thanks in advance for your help.

Solution

Related threads: 1 2

Here's the fix I am using:

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

Milen A. Radev · Accepted Answer · 2010-07-01 22:34:10Z

11

LIKE is a case-sensitive operator in Postgres. For case-insensitive pattern matching use ILIKE or other standard methods.

answered Jul 1, 2010 at 22:34

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

studiofellow Over a year ago

The query being case-sensitive never even crossed my mind. Thanks so much. I ended up setting everything to lowercase in the query because it's compatible with both types of databases. Edit: Added final code to the question.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related