Altering enum type

Issue #270 wontfix
Derek Paterson
created an issue


Is there a method for altering an existing enum type as opposed to creating one?

Can't find an example anywhere.


Comments (7)

  1. Derek Paterson reporter

    So use raw SQL? I'm using MySQL by the way.

    So perhaps using reference tables is better than using enums anyway, so perhaps thats the best solution.

    Thanks anyway.

  2. Michael Bayer repo owner

    For MySQL, this is just a change in column type. Do an ALTER COLUMN and set the new type:

    op.alter_column("my_table", "my_column", existing_type=ENUM(...), type_=ENUM())
  3. Mischa

    I would really really like to see a cleaner way to ALTER TABLE for postgresql enums. It's quite unpleasant right now. The rest of alembic works great, this is the only thing that sticks out as gnarly.

  4. Jens Troeger

    @Michael Bayer, thanks for your above recommendations. (Related and older Stackoverflow questions are here and here.)

    I’m currently looking into a very similar problem: adding values to an existing Enum column, and migrating data along. Currently, our DB is MySQL but I’m planning to switch to PostgreSQL within the next few months.

    Question: your suggestion to op.alter_column() will not modify any row data, correct? So if the change removes existing Enum values, what happens, particularly if the column can’t have NULL values. I assume that in such truncating cases I’d have to manually iterate over the existing Enum values and map them to the new set?

  5. Mischa

    There is a mechanism to map values to different enums without iterating, using a SQL "CASE WHEN" statement to remap. This is done commonly when converting a TEXT or INTEGER value to an enumerated value

  6. Log in to comment