Besides not being able to rename enum values there are two other
limitations regarding enums which would be nice to get finally fixed:
1) There is also no possibility to drop a value.
2) Quoting the docs (
"ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type)
cannot be executed inside a transaction block." Example:
# CREATE TYPE bogus AS ENUM('good');
# ALTER TYPE bogus ADD VALUE 'bad';
ERROR: ALTER TYPE ... ADD cannot run inside a transaction block
To summarize it:
For enums to finally be really usable it would nice if we would have (or
ALTER TYPE name DROP VALUE [ IF EXISTS ] enum_value
ALTER TYPE name RENAME VALUE [ IF EXISTS ] old_enum_value_name TO
And all of the operations (adding, renaming, dropping) should also work
when done within a new transaction on an enum that existed before that
I did some digging and maybe following commits are useful in this context:
Also there are these discussions where some of the messages contain some
Also have a look at this workaround:
How high is the chance that given the above information someone will tackle
these 3 issues/requests in the near future? It seems there were some
internal chances since the introduction of enums in 8.x so maybe this
changes wouldn't be that disruptive anymore?
On 9 March 2016 at 18:13, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Andrew Dunstan <and...@dunslane.net> writes:
> > On 03/09/2016 11:07 AM, Tom Lane wrote:
> >> I have a vague recollection that we discussed this at the time the enum
> >> stuff went in, and there are concurrency issues? Don't recall details
> >> though.
> > Rings a vague bell, but should it be any worse than adding new labels?
> I think what I was recalling is the hazards discussed in the comments for
> RenumberEnumType. However, the problem there is that a backend could make
> inconsistent ordering decisions due to seeing two different pg_enum rows
> under different snapshots. Updating a single row to change its name
> doesn't seem to have a comparable hazard, and it wouldn't affect ordering
> anyway. So it's probably no worse than any other object-rename situation.
> regards, tom lane