CODEWITHSUNDEEP
ruby-on-railspostgresqlcase-insensitivesql-like
antpaw
antpaw

Reputation: 15985

Postgres case insensitive searching with Rails

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

Upvotes: 11

Views: 9839

Answers (3)

Adam Lassek
Adam Lassek

Reputation: 35505

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.

Upvotes: 4

Sean
Sean

Reputation: 10206

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

Upvotes: 4

atrain
atrain

Reputation: 9255

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

Upvotes: 42

Related Questions