Alter TYPE RENAME VALUE works in Postgres 10 but not in Postgres 9.6?

Question

I am using Postgres 10.3.

Based on this question, I use:

ALTER TYPE name RENAME VALUE attribute_name TO new_attribute_name

to rename an enum value.

But I need a solution that works with Postgres 9.6 that does not require updating pg_enum manually because it needs permissions that I don't have.

Sonneteer

Using enums is a bad idea to begin with they contradict the basic rules of database normalization. – Zeller

Then what is best approach for storing enum from hibernate to postgres? – Sonneteer

The correct database design for things like that is to use a one-to-many relationship between the main table and the "enum values". I am sure your obfuscation layer is able to deal with that, if not you should re-consider the use of an ORM. – Zeller

Answer 1

There is no supported way to rename an enum value in PostgreSQL 9.6.

Directly modifying pg_enum is something you should not only rule out because of permission issues, but also because directly messing with the system catalogs is dangerous and may destroy your data.

You should use enums with care. They are only good if they never need to be modified. If there is a chance that the enum values won't be immutable, use a lookup table instead.

Dissogeny

Thanks LaurenzAlbe. But according to postgresql 9.6 documentation ew can do alter enum: postgresql.org/docs/9.6/static/sql-altertype.html – Sonneteer

Sure, you can add new values, but where in the documentation do you see that you can rename them? RENAME ATTRIBUTE is for composite types, not for enums. – Dissogeny

this means that we can't rename enum value in postgres 9.6 with above case? – Sonneteer

Exactly. I thought I made that clear. The documentation says so too. – Dissogeny