citext Collumn Not Matching in Ecto Query

Hello! I am trying to implement the citext extension for the email field of my database.
I created the following migration:

    execute "CREATE EXTENSION citext"

    alter table :credentials do
      modify :email, :citext
  def down do
    alter table :credentials do
      modify :email, :string

    execute "DROP EXTENSION citext"

As a test, I added the address to my table. Running

 Repo.get_by(Credential, email: "")

returns the correct value, as expected, but the following Ecto query returns nil:

def get_user_by_email(email) do
    from(u in User, join: c in assoc(u, :credential), where: == ^email)
    |> Repo.preload(:credential)

Am I missing something here? Is == not the correct operator to use in this case?

That query seems correct - can you verify what SQL it ultimately sends to the database?

Also verify that the DB you’re trying it on shows the correct type in psql - I’ve spent too long chasing my tail when the test DB was missing a migration that had run in dev :scream_cat:

1 Like

Huh. Got back to work today, and everything seems to be working as expected? Maybe all I needed to do was restart the dev-environment?
:thinking: A classic case of “Have you tried turning it on and off again?”…