CODEWITHSUNDEEP
postgresqlenumsdatabase-migration
Max Yankov
Max Yankov

Reputation: 13327

How to write a down migration for Postgresql where up migration adds an enum value?

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.

Upvotes: 6

Views: 2001

Answers (1)

Evan Carroll
Evan Carroll

Reputation: 1

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

Upvotes: 4

Related Questions