Using Postgres Enums in Ecto Migrations

October 16, 2018

PostgreSQL Enum types are pretty great.

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 decided to do 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. Below is what worked for my migration, simplified and with some complexity and details anonymized.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
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 necessary in this example.
  end
end
elixir ecto postgres