Using Postgres Enums in Ecto Migrations

PostgreSQL Enum types are pretty great but not supported in Ecto. gjaldon/ecto_enum makes them super easy to use in an Elixir project with Ecto. I was recently refactoring an application after settling on functionality and meaning of a few core concepts, and I decided to move a few related options into an enum that more closely matches what users think.

I did this in three migrations: one to add the new enum and field, one to determine the new value of the field, and one to set the default of the field.

While writing the migration to determine the value of the new field for existing rows in the table, I ran into (Postgrex.Error) ERROR 42804 (datatype_mismatch): column "phase" is of type phase but expression is of type text. I was executing update example set phase = 'first' when foo = 't';. Postgres expects values of the phase type, not text here.

The solution was to cast the value to the phase enum type. Also, this action and the complimentary action could be done in a single query. Below is what worked for my migration, simplified and with some complexity and details anonymized.

defmodule MyApp.Repo.Migrations.SetPhase do
  use Ecto.Migration

  def up do
    execute """
      update example set phase = case
        when foo = 't' then 'first'::phase
        when foo = 'f' then 'second'::phase
      end;
    """
  end

  def down do
    # Rollback action not included
  end
end