How the same query gives different results in different Rails environments

Question

I've deployed my RoR app into heroku and I'm trying to understand why a table when ordered alphabetically is case sensitive on my development environment and case insensitive on production environment.

Is this related to some difference in the configuration file of each environment, or is because I'm using sqlite in development env and postgresql in production?

Or due to something else?

Thanks.

Answer 1

Sort order in PostgreSQL is defined by the locale settings. Start by reading the manual here:

22.1. Locale Support

Normally, Postgres sort order is case sensitive. I don't know of a "case insensitive" locale. There is the data type citext, but you would know if you are using that.

• Deferrable, case-insensitive unique constraint

Sort order in SQLite is not necessarily the same. Never develop with a different RDBMS than you use in production. You are not the first to run into problems with this approach:

• Generic Ruby solution for SQLite3 "LIKE" or PostgreSQL "ILIKE"?