Changes to Postgres enum types?

[hiaw]

can I reopen this again? I tried out the suggested method and I got an error that I don't know how to proceed.

22:03:54.325 [info]  execute "ALTER TYPE gender ADD VALUE 'other'"
** (Postgrex.Error) ERROR 25001 (active_sql_transaction): ALTER TYPE ... ADD cannot run inside a transaction block
    (ecto) lib/ecto/adapters/sql.ex:195: Ecto.Adapters.SQL.query!/5

And it is documented in postgresql docs. https://www.postgresql.org/docs/9.1/static/sql-altertype.html

The following is my migration file

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

  def up do
    Ecto.Migration.execute "ALTER TYPE gender ADD VALUE 'other'"
  end

  def down do
    Ecto.Migration.execute "ALTER TYPE gender DROP VALUE 'other'"
  end
end

[gjaldon]

From the notes in the postgresql docs you linked above:

ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block.

Since ALTER TYPE .. ADD VALUE can't be executed in a transaction, you'll need to add @disable_ddl_transaction true to your migration like:

defmodule MyApp.Repo.Migrations.AddToGenderEnum do
  use Ecto.Migration
  @disable_ddl_transaction true

  def up do
    Ecto.Migration.execute "ALTER TYPE gender ADD VALUE 'other'"
  end

  def down do
    Ecto.Migration.execute "ALTER TYPE gender DROP VALUE 'other'"
  end
end

Does that fix your problem?

[hiaw]

Yes. Awesome!

[hiaw]

Just a note for people who wants to do thi sin the future. It is not very hard to rollback this migration. If you did roll back using the following you wouldn't be able to migrate again cause ecto will give you this error ** (Postgrex.Error) ERROR 42710 (duplicate_object): enum label "other" already exists

defmodule SmishScapholdModel.Repo.Migrations.AddToGenderEnum do
  use Ecto.Migration
  @disable_ddl_transaction true

  def up do
    Ecto.Migration.execute "ALTER TYPE gender ADD VALUE 'other'"
  end

  def down do
  end
end

The correct way to roll back is here but it's too complicated for now.

[gjaldon]

Thanks for the info and the link @hiaw!