Re: How to modify ENUM datatypes?

On Tue, 22 Apr 2008 20:58:30 +0800 Craig Ringer wrote:

> D. Dante Lorenso wrote:
> >
> > The problem is that once I create a column in my account table that
> > uses this 'account_type' datatype, I can't seem to change or add to it
> > any more. I want to add a new value or edit/delete an existing one.
> >
> > How do you make changes to an ENUM datatype that is already in use?
> >
> As far as I know ENUM is not well suited to uses where new enumeration
> members may be added later. A lookup table and a foreign key is probably
> better for this sort of use.

I remember the discussions before PG implemented ENUMs at all - some
people voted against this "feature" because they knew that questions
about modifing the enum values would pop up sooner or later.

> You *can* add elements to an enum type - sort of - by creating a new
> type and converting columns. It's ugly, though, and will be hard to get
> right when the column of interest is referenced by foreign keys and such.

If there's one request to modify a specific ENUM column, other requests
will follow because the enum set is not as static as it seems in the
first place. So i beg that the only advise should be "change the column
to a foreign key construct". Converting columns to new data types is
much more overhead anyway.

Kind regards

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group