6

I want to expand my enum type by adding a new enum value. This is pretty straightforward to do. I also keep all my SQL in migrations, and pair each up migration with a symmetric down migration that reverts the schema to the previous state.

However, reverse operation — removing a variant from enum — is not supported.

What's the common method around this? I can see two options:

  1. Write "up" migration with if not exists clause and do nothing in the "down" migration. I don't like this option because it violates the assumption that applying and reverting a migration leaves the schema in the same state as it was before.
  2. Convert to a new enum type in "down" migration, as described in the question linked above — seems like an overkill for such a simple operation.
Max Yankov
  • 12,551
  • 12
  • 67
  • 135
  • 3
    Another good example why enums are usually not such a good idea. This would be very easy if you used a standard lookup table with a foreign key. Removing the value would as simple as running a `DELETE` statement –  May 08 '18 at 10:18
  • Is "migration", in the context of this question, a technical term, or a word you use to describe a process which means something to you but not to me? – 404 May 08 '18 at 10:18
  • 2
    @eurotrash https://en.wikipedia.org/wiki/Schema_migration – Max Yankov May 08 '18 at 10:20
  • @a_horse_with_no_name good point. However, for now I think that enums with a constant amount of variants that map 1-to-1 to enum variants in the application language fit my needs better than a foreign key – Max Yankov May 08 '18 at 10:21

1 Answers1

4

You can not migrate an ENUM. Your opinions and assessments are 100% correct.

I don't like this option because it violates the assumption that applying and reverting a migration leaves the schema in the same state as it was before.

If that's a hangup, then the option is out of the picture. Because you're correct.

seems like an overkill for such a simple operation.

That's because deleting a value from an ENUM is not a simple operation. So why would migrating an ENUM with more values, to one with fewer values be a simple operation?

Listen to the horse,

Another good example why enums are usually not such a good idea. This would be very easy if you used a standard lookup table with a foreign key. Removing the value would as simple as running a DELETE statement – a_horse_with_no_name

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468