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
