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
    end
  end
  def down do
    alter table :credentials do
      modify :email, :string
    end

    execute "DROP EXTENSION citext"
  end

As a test, I added the Email.Case3@test.com address to my table. Running

 Repo.get_by(Credential, email: "email.case3@test.com")

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: c.email == ^email)
    |> Repo.one()
    |> Repo.preload(:credential)
  end

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?”…