Postgres case insensitive searching with Rails

Question

My development database is SQLite but I deploy my app to Heroku and they are using PostgreSQL.

Now sometimes I have two different results coming out if I perform searches because PostgreSQL is case-sensitive but SQLite is not.

Shouldn't Rails standardize these things? What methods should I use to solve that?

Here is how to fix it with raw SQL

I'd recommend developing on top of PostgreSQL if you're deploying to Heroku. No ORM will protect you from database-specific behavior. — mu is too short
– mu is too short, Commented Jul 31, 2011 at 20:48
And I like using SQLite locally and Postgre on shipping, so I'm not tied to an certain DB. to each his own. — Tom Andersen
– Tom Andersen, Commented Feb 9, 2012 at 16:25

atrain · Accepted Answer · 2011-07-31 20:30:29Z

42

Case insensitive searching in Postgres:

use ilike instead of like (case-insensitive like)
if you want to use =, make both sides either UPPER or LOWER

answered Jul 31, 2011 at 20:30

atrain

9,2951 gold badge39 silver badges40 bronze badges

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

3 Comments

antpaw Over a year ago

Thank you! The problem with like is that i don't know how to chain them with the OR operator. Is it save to use this with every supported db by rails? "LOWER(title) LIKE LOWER('%#{search_term}%')"

antpaw Over a year ago

well it doesnt brake my code in mysql, sqlite and pgsql so its good enough, thanks!

Jan Hettich Over a year ago

use UPPER or LOWER if you want to run the same code on both MYSQL and POSTGRES (although that is obviously not best practice!)

Sean · Accepted Answer · 2011-07-31 23:53:27Z

4

Another DDL way of handling this is the citext data type, or case-insensitive TEXT.

answered Jul 31, 2011 at 23:53

Sean

10.3k4 gold badges42 silver badges44 bronze badges

Comments

Adam Lassek · Accepted Answer · 2016-05-18 21:12:44Z

4

There are many relatively common things that ActiveRecord doesn't handle, and LIKE matching is one of them. Here is how to achieve this using straight Arel instead.

Model.where(
  Model.arel_table[:title].matches("%#{search_term}%")
)

You can install Arel-Helpers to make this a little easier

Model.where(Model[:title].matches("%#{search_term}%"))

I previously recommended Squeel for this, but the original creator has stopped supporting it and there doesn't seem to be a full-time developer maintaining it. And since it plays around with private ActiveRecord APIs, it needs constant tending.

edited May 18, 2016 at 21:12

answered Jul 17, 2013 at 5:13

Adam Lassek

35.5k14 gold badges94 silver badges109 bronze badges

2 Comments

kgx Over a year ago

Great solution, and Squeel is awesome, but just a heads up that this is Postgres-specific behavior. If you are looking to do this for other RDBMS the output may be case sensitive.

Adam Lassek Over a year ago

@kgx actually this example will work as intended in both cases. As @antpaw says above, SQLite LIKE is case-insensitive by default. So this will use LIKE in SQLite and ILIKE in PostgreSQL.

Collectives™ on Stack Overflow

Postgres case insensitive searching with Rails

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related