Oct 17, 2014

Case insensitive LIKE in postgres (or why it's important to use the same DB for test and prod)

I recently ran into a production bug where a simple 'like' query wasn't working properly, even though I had passing tests which explicitly tested this case.

The problem, of course, is that I was using different databases: sqlite on my dev machine, and postgres on the production machine.

# case insensitive on sqlite
# BUT case sensitive on postgres
where(["description LIKE ?", like_value])

In postgres you need to use 'ILIKE' for case insensitive queries.

# case insensitive on postgres
where(["description ILIKE ?", like_value])

The more important takeaway, however, is to make sure that you use the same database in your test env as do in production!