Andrew Dunstan <andrew@dunslane.net> writes:
> Robert Haas wrote:
>> I like the idea of being able to modify enums on the fly, but I'm
>> skeptical of an implementation that won't always work. Maybe it's
>> still better than what we have now, but it seems grotty.
> I'd be perfectly happy to hear a reasonable alternative.
Insert a sort order column into pg_enum, and rearrange the values in
that whenever the user wants to add a new value in a particular place.
You give up cheap comparisons in exchange for flexibility. I think lots
of people would accept that tradeoff, especially if they could make it
per-datatype.
One point here is that you'd have to restrict the rearrangements so that
the relative sort order of existing values never changes, else you break
(for example) indexes on columns of that type.
regards, tom lane