Changes to Postgres enum types?

[DCameronMauch]

Hello. Thanks so much for this library. We decided that we want to use real Postgres enums. So the initial setup is easy and works just fine.

But we have the question, what does it look like if you want to add something to the enum later? I am currently thinking that would just be an update to the defenum and a migration. But not really clear.

[gjaldon]

@DCameronMauch hi there! Sorry it took a while to respond.

You're correct that you'll need to add a migration and then adding the attribute to defenum. This means you'll need to recompile for the changes to apply. The query for the migration would look something like:

ALTER TYPE the_type ADD ATTRIBUTE new_attribute int;

[gjaldon]

I'm closing this but feel free to post any more questions here @DCameronMauch

[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 add_type() do
    sql = "ALTER TYPE gender ADD VALUE 'other'"
    Ecto.Migration.execute sql
  end

  def drop_type() do
    sql = "ALTER TYPE gender DROP VALUE 'other'"
    Ecto.Migration.execute sql
  end

  def up do
    add_type
  end

  def down do
    drop_type
  end
end

[PJUllrich]

For those who come to this issue: As described in the docs you have to add @disable_ddl_transaction true to the migration which adds/removes the enum type.