Generic Ruby solution for SQLite3 “LIKE” or PostgreSQL “ILIKE”?

by

The root of the problem lies here:

I am using SQLite3 for development and PostgreSQL for deployment.

That’s a bad idea™. You will keep running into incompatibilities – or worse: not realize some until damage is done.
Use the same RDBMS (PostgreSQL) for development and production and save yourself the pointless trouble.

While you are stuck with your unfortunate setup, there is a simple fix:

lower(style) LIKE lower(?)

Works on both platforms alike.

Drop the right-hand lower() if you provide a lower-case search-pattern.

In standard SQLite lower(X) only folds ASCII letters. Quoting the chapter Core Functions in the SQLite manual:

The lower(X) function returns a copy of string X with all
ASCII characters converted to lower case. The default built-in lower()
function works for ASCII characters only. To do case conversions on
non-ASCII characters, load the ICU extension.

Bold emphasis mine.

PostgreSQL lower(X) works with UTF-8 out of the box.

As a welcome side effect, you can speed up that query in PostgreSQL with an index on the expression lower(style), which will be faster than using ILIKE and a basic index on style.

Also, since PostgreSQL 9.1 you can use a GIN or GIST index with the pg_trgm extension to speed up any LIKE and ILIKE query – trigram indexes are case-insensitive. Details:

More Related Contents:

  1. Change from SQLite to PostgreSQL in a fresh Rails project
  2. GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function
  3. Boolean Validation
  4. Can’t find the ‘libpq-fe.h header when trying to install pg gem
  5. PostgreSQL gapless sequences
  6. Deploying RoR app to Heroku with SQLite 3 fails
  7. Getting error: Peer authentication failed for user “postgres”, when trying to get pgsql working with rails
  8. PG::ConnectionBad – could not connect to server: Connection refused
  9. Can not connect to local PostgreSQL
  10. Rails: Installing PG gem on OS X – failure to build native extension
  11. How to handle Ruby on Rails error: “Please install the postgresql adapter: `gem install activerecord-postgresql-adapter'”
  12. Clean way to find ActiveRecord objects by id in the order specified
  13. Heroku deployment failed because of sqlite3 gem error
  14. HEROKU – cannot run git push heroku master [duplicate]
  15. docker postgres with initial data is not persisted over commits
  16. Can I write PostgreSQL functions on Ruby on Rails?
  17. New data not persisting to Rails array column on Postgres
  18. Pushing Rails with SQLite3 to Heroku fails [duplicate]
  19. Rails + Postgres drop error: database is being accessed by other users
  20. PostgreSQL: FATAL – Peer authentication failed for user (PG::ConnectionBad)
  21. Store the day of the week and time?
  22. PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects
  23. Rails: FATAL – Peer authentication failed for user (PG::Error)
  24. Changing a column type to longer strings in rails
  25. Cannot install pg gem in Mavericks with Postgres.app
  26. Why can’t I install the SQLite gem?
  27. Adding foreign key to a rails model
  28. sqlite3 gem fails to install
  29. Ruby on Rails: Advanced search
  30. An error occurred while installing pg (0.17.1), and Bundler cannot continue

Leave a Comment