Re: What is the good equivalent for ENUM ?

>>>>> "SD" == Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:

SD> On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote:
>> The problem is that this MySQL database uses ENUM, do you see what can I
>> do to migrate ENUM into PostgreSQL ?

SD> varchar with check constraints. Add constraits to allow only
SD> certain values of varchar string.

I used to do this.  It turns out to be horribly inflexible when you
need to alter the enum values since the constraints cannot easily be
changed.

What I do is create a short table for the enum like this:

CREATE TABLE status_levels (
 status varchar(10) PRIMARY KEY
) WITHOUT OIDS;
INSERT INTO status_levels (status) VALUES ('active');
INSERT INTO status_levels (status) VALUES ('overdue');
INSERT INTO status_levels (status) VALUES ('suspended');
INSERT INTO status_levels (status) VALUES ('terminated');

then reference it via foreign key from the "enum" field:

CREATE TABLE whatever (
 ...
 status varchar(10) NOT NULL DEFAULT 'active' REFERENCES status_levels(status),
 ...
);




--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/