CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
CodeSmith
CodeSmith

Reputation: 2233

Why is Rails query case sensitive in production but not development?

While I debugging a Rails 5.0.1 app today, I discovered the following unexpected issue. I have a user in the database who has an email of [email protected]. When I run 

User.find_by(email: "[email protected]")

in production, I get a nil back. However, if I execute

User.find_by(email: "[email protected]")

it successfully finds the record. The weird part, is this doesn't happen when the Rails app is running in development mode. I can query with [email protected] or [email protected] and it always finds the record.

What could be causing Rails to make a case sensitive search in production? The database is Postgres. All of this is being tested on the same machine with the same code.

Update 2/10/2017

Unfortunately, the problem went away after rebuilding my development environment. After reading some of the comments below, case sensitive searches in production is the expected behavior for Postgres/Rails. It's still unclear as to why my development environment (on the same machine) was performing case insensitive searches. I'll update if I find out any more info...

Upvotes: 0

Views: 464

Answers (2)

Shannon
Shannon

Reputation: 3018

Postgres is case sensitive by default. You can use ILIKE to avoid case sensitivity.

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

Or change both to lower case then search:

User.where("LOWER(email) = ?", email.downcase)

Upvotes: 6

7urkm3n
7urkm3n

Reputation: 6311

I am not sure, why exactly does it. But there's simple solution.

Put into a model this.

Ex: user.rb

class User < ActiveRecord::Base
  before_save { self.email = email.downcase }
end

Upvotes: 1

Related Questions