I want to create a case insensitive unique column in my database for the username. I want the user who registers as “Dr.Doom” to be displayed with that case and no other user can be called e.g. “dr.doom”.
Unfortunately, different databases provide different guarantees when it comes to case-sensitiveness. For example, in MySQL, comparisons are case-insensitive by default. In Postgres, users can define case insensitive column by using the :citext type/extension.
so in the migration I defined a column
add :username, :citext, null: false
but when I do mix ecto.migrate I get
(Postgrex.Error) ERROR (undefined_object): type “citext” does not exist
(ecto) lib/ecto/adapters/sql.ex:187: Ecto.Adapters.SQL.query!/5
(ecto) lib/ecto/adapters/postgres.ex:71: Ecto.Adapters.Postgres.execute_ddl/3
…etc
I am using Postgres
What I am doing wrong? Also in the documentation where can I find the various field types that are available?
One other advantage that I found with using the citext column type is that I didn’t have to String.downcase my input data or wrap it in a postgres call to lower when querying. In other words I could insert a column with the username mmmries and then if a user searched for MmmrieS it would still find my record. It’s nice to have this automatically done so I don’t forget to downcase a piece of input later.
In case you’re wondering what your migration and schema might look like, consider this example where a “status” resource has a unique “slug” field that should be case-insensitive.
# Migration:
create table(:examples, primary_key: false) do
add :slug, :string, size: 10, primary_key: true
add :name, :string, size: 32, null: false
end
create index(:examples, ["lower(slug)"], name: :examples_index, unique: true)
# Schema
use Ecto.Schema
import Ecto.Changeset
@primary_key {:slug, :string, autogenerate: false}
schema "examples" do
field :name, :string, size: 32
end
The changeset can define the slug as a read-only field, e.g.
validate_change(:slug, fn :slug, _ -> [slug: "slug is a read-only field"] end)
And to add to the suggestion: in case one might want to replace a “normal” unique index with one like this (for example on a username column) that could be something like this inside a migration in the simplest case for reference:
I wanted to do the same as the OP but wanted to enable the citext extension using a migration and found I could do the following:
defmodule MyApp.Repo.Migrations.CreateUsers do
use Ecto.Migration
def change do
execute "CREATE EXTENSION IF NOT EXISTS citext", "DROP EXTENSION citext"
create table(:users) do
add :email, :citext, null: false
add :password_hash, :string, null: false
timestamps()
end
create unique_index(:users, [:email])
end
end