Migrating email field from string to citext

What is citext?

Postgres provides a column extension called CITEXT (Case-Insensitive Extension). The entension allows us to create fields of type citext in postgresql. As the name suggests, it allows us to query fields of type citext in a case-insensitive manner.

Assuming a table exists named User with a field name of type citext

User.where(name: "Test")
User.where(name: "test")

Both the above queries will return the same output. The reason for this is, that Postgres does a case insensitive comparison of CITEXT columns by converting the query string and the value of the comparing column to lowercase using LOWER.

Note: - The data on the database is not exactly stored in lowercase even though it may seem so. - Querying CITEXT column is slower than a TEXT or STRING column because of the underlying lowercase conversion.

Why have the email field as citext type?

Emails are case insensitive and thus should be treated the same way. Ideally when someone provides an email, for example, Test@example.com or test@example.com, they shouldn’t be differentiated. They need to be considered the same and querying through them should eventually lead to the same result.

With the citext extension in PostgreSQL, any comparisons on a column of citext type will internally have the lower function executed on the arguments. Thus, it's ideal to have email fields of citext type.

Migrating Email column to citext

To add or change a column to citext, we must first enable the citext extension using the following command in a migration:

def change
  enable_extension 'citext'
end

After enabling the citext extension, create migrations as follows: To add a citext column in a new table:

def change
  create_table :MODEL do |t|
    t.citext :COLUMN_NAME

    t.timestamps
  end
end

To add a new column/change an existing column to citext type:

def change
  add_column :MODEL, :COLUMN_NAME, :citext
  change_column :MODEL, :COLUMN_NAME, :citext
end

Finally, run rails db:migrate

Citext Migration

Migrating email field from string to citext

What is citext?

Why have the email field as citext type?

Migrating Email column to citext