Re: How to modify ENUM datatypes?

[Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx>]

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.

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.

One way to do it if you really must:

-- Starting state

CREATE TYPE et1 AS ENUM('yes','no');
CREATE TABLE testtab (
   a et
);
INSERT INTO testtab (a) values ('yes');

-- Change
CREATE TYPE et2 AS ENUM('yes','no','filenotfound');
ALTER TABLE testtab ALTER COLUMN a TYPE et2 USING (
  CASE a WHEN 'yes' THEN 'yes'::et2 WHEN 'no' THEN 'no'::et2 END
);

-- Alternative ALTER that's suitable if you're not removing anything 
from the enum
ALTER TABLE testtab ALTER COLUMN a TYPE et2 USING ( a::text::et2 );

Personally, though, I'd stick to the good 'ol lookup table and foreign key.

--
Craig Ringer