Skip site navigation (1) Skip section navigation (2)

Re: How to modify ENUM datatypes?

From: Andreas 'ads' Scherbaum <adsmail(at)wars-nicht(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: How to modify ENUM datatypes?
Date: 2008-04-22 14:31:22
Message-ID: 20080422163122.7f8ac1e5@iridium.wars-nicht.de (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: mateo21Date: 2008-04-22 14:36:27
Subject: Bitmap Heap Scan takes a lot of time
Previous:From: David WilsonDate: 2008-04-22 14:26:29
Subject: Re: table as log (multiple writers and readers)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group