Re: How to modify ENUM datatypes?

Andreas 'ads' Scherbaum wrote:
> 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.

So, the advice here is "don't use ENUM"?

I was really hoping that it would be more efficient to not have to do
all the foreign keys and joins for tables that may have 4-5 enum types.

Just being able to:

SELECT *
FROM tablename

would be nice if my columns contained enums instead of doing:

SELECT *
FROM tablename, lookuptable
WHERE tablename.some_id = lookuptable.some_id

Isn't the join more expensive?

-- Dante