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
