Text fields in Postgres are case sensitive. This is a problem when storing emails because
Coutermarsh.Mike@Gmail.com are not equal.
If you do any lookups by email address, you have a few options.
- always use downcase on write
- use a functional index, lower(email)
- use citext
Here are my experiences with each of them.
Option 1: Use downcase (not ideal)
email.downcase each time you store an email or do a lookup. This works well, but you (+ your entire team) will have to always remember to do this.
Option 2: Use a functional index, lower(email) - (also, not ideal)
The 2nd solution is to create a functional index with
lower(email) and forever have to query ActiveRecord like this:
User.where("lower(email) = ?, email"). This is my least favorite solution. It seems like it would work really well. But in practice can cause a lot of problems when combined with ActiveRecord. You can no longer use
create in the ways you expect to.
User.create(attributes) is very common in a Rails app. If you require email addresses to be
unique (which you probably do). Rails will run a query first to see if a record exists.
SELECT 1 as ONE FROM users WHERE email = 'email@example.com' LIMIT 1;
See the problem in that query? Our index is on
lower(email). This query misses the index and you’re now doing a full table scan each time a user is created.
Other seemingly innocent ActiveRecord queries will also miss your index. Here’s a common one:
The problem with lower is similar to using downcase, you & your entire team have to remember to always use lower.
Option 3: Use Citext!
Here’s my favorite solution (this is what we use at Product Hunt).
The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text. – Postgres Docs
Citext makes your email column case-insensitive.
If you want to change an existing column to Citext. Here’s an example Rails migration.
# Example Rails migration class ChangeEmailToCitext < ActiveRecord::Migration def change enable_extension("citext") change_column :users, :email, :citext ## **Warning!** changing a columns datatype locks the table. This can cause ## downtime in production (your app will not be able to write while the change is ## being made). Read: https://www.braintreepayments.com/blog/safe-operations-for-high-volume-postgresql ## If locking the table is not an option for you, **do not** use this migration. You'll ## instead need to create a new citext column, change your code to write to both, backfill the new ## column and then switch reads/writes to the new column. end end
Remember to add an index to this column.
class AddIndexToUsersEmail < ActiveRecord::Migration disable_ddl_transaction! def change add_index :users, :email, unique: true, algorithm: :concurrently end end
I hope you find this helpful! If you’ve come across any better solutions, or have ways to improve this one, please comment below.