Update PostgreSQL enums with Rails migrations

Our data model at Pretto rely a lot on enums.

The standard solution in Rails would be to use ActiveRecord::Enum However, casting the values as integer in the database is not very human-readable, and some of our entities have multiple attributes with a common enum value. That’s why we decided to use PostgreSQL Enumerated types.

We had a beautiful honey moon with them until we had to change one label. Here is the workaround we built.

tl; dr

Spoiler Alert: do not use the helper of the tl;dr section.

Migration helpers
Sample migration


What do we do with the helper:

  • Add the new label to the enum
  • Update the column linked to the enum (each old label should become the new label)
  • Alter the enum to delete the old label
NB: it is not possible to add a new label to an enum in a transaction, we must add self.disable_ddl_transaction! in the migration to use the helper.

It seems to be OK, but:

  • it is a bad practice to update the pg_enum table
  • if you are working with Heroku you don’t have the permission to update this table
  • running the migration out of transaction means that we are f***ed if it somehow fails in the middle (and of course it happened)

That’s why we are not using the helper described above — even if it’s something we can find all over StackOverflow. Instead we use:

Migration helper (final)

In details:

  • Fetch the enum labels from the data base
  • Add the new label to the enum
  • Rename the enum
  • Create a new enum with the new labels
  • Update the table to set each old value to the new one
  • Update the table to map the column to the new enum
  • Drop the old enum
It didn’t solved the transaction problem, so if you have a workaround for this feel free to comment :)



Migration helper spec