This is too hard to put into a sql file and run with your migrations tool?

    BEGIN;
        ALTER TYPE my_enum ADD VALUE 'bar' AFTER 'foo';
    COMMIT;

From the docs (as I suspect you already know): ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block

Also, removing a value is a pain (though, that may have changed now).

I know you can do it — but I've found after trying a bunch of approaches that text with constraints is a better starting point. Just wanted to point it out because it's something I researched a fair amount. Many guides say that enum can be a pain but I decided that I'd use them to be pure, then discovered they were a pain, and now don't use them as a starting point.

Yeah, this doesn't actually work. You'll get this error:

ERROR: ALTER TYPE ... ADD cannot run inside a transaction block

FWIW, this is fixed in the upcoming Postgres 10:

"Reduce locking required for adding values to enum types (Andrew Dunstan, Tom Lane)

Previously it was impossible to run ALTER TYPE ... ADD VALUE in a transaction block unless the enum type was created in the same block.

Now, only references to uncommitted enum values from other transactions are prohibited."

https://www.postgresql.org/docs/devel/static/release-10.html...

Sweet. That's certainly a nice improvement :). If removing ENUM values will be supported at some point as well, I can see myself recommending them unconditionally.

Would you be ok if dropping a value were to just mark it as 'deleted' from the catalogs, and no new values could be assigned? The reason enums have these weird restrictions is that they can appear in indexes, even after a value has been deleted (or its creation rolled back). If we don't know how to compare them after deletion, the index can't be correctly traversed anymore...

2 Questions! What's the index data structure that holds old enum values even after the value itself is not used anymore?

Secondly, what are your thoughts on using enum generally? Would you recommend them in cases where you don't need to really optimise for narrower columns?

> 2 Questions! What's the index data structure that holds old enum values even after the value itself is not used anymore?

It's not that index structure specific atm, even though it could possibly be avoided for some types (e.g. hash, although there's considerable visibility issues to make that possible). Consider e.g. a btree index, if the deleted enum value ends up in an inner page, you really need to know how it compares to know where to descend to.

> Secondly, what are your thoughts on using enum generally? Would you recommend them in cases where you don't need to really optimise for narrower columns?

I like using them. There's cases where the transactional restrictions make it too problematic, but other than that I think the documentational advantage is substantial, besides just the width.

Yeah, I think that'd be reasonable. My main motivation for using ENUMs is type safety and small column width. What you're proposing sounds like it would keep both of these advantages :)