Postgres Pro
Downloads
Home   >   mailing lists

Thread: What is the good equivalent for ENUM ?

What is the good equivalent for ENUM ?

From
"Bruno BAGUETTE"
Date:
Hello,

I have to migrate a MySQL database to a PostgreSQL database without
procedures.

The problem is that this MySQL database uses ENUM, do you see what can I
do to migrate ENUM into PostgreSQL ?

Thanks in advance :-)

-------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net

Re: What is the good equivalent for ENUM ?

From
"Shridhar Daithankar"
Date:
On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote:

> Hello,
>
> I have to migrate a MySQL database to a PostgreSQL database without
> procedures.
>
> The problem is that this MySQL database uses ENUM, do you see what can I
> do to migrate ENUM into PostgreSQL ?

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

Check http://archives.postgresql.org/pgsql-sql/2002-03/msg00273.php

Bye
 Shridhar

--
All new:    Parts not interchangeable with previous model.

Re: What is the good equivalent for ENUM ?

From
Vivek Khera
Date:
>>>>> "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/

Re: What is the good equivalent for ENUM ?

From
Dennis Gearon
Date:
that's how I'd do it, since in reality, that's all an ENUM is, is a mini
foreign table internalized onto a column in another table.

Vivek Khera wrote:

>>>>>>"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),
> ...
>);
>
>
>
>
>
>

Re: What is the good equivalent for ENUM ?

From
Ron Johnson
Date:
On Wed, 2003-09-03 at 09:50, Vivek Khera wrote:
> >>>>> "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.

It'll be better when domains have alterable constraints.  Your
way is the traditional (and best, IMO) way, though.

> 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),
>  ...
> );
>

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"You ask us the same question every day, and we give you the
same answer every day. Someday, we hope that you will believe us..."
U.S. Secretary of Defense Donald Rumsfeld, to a reporter

Re: What is the good equivalent for ENUM ?

From
Bruce Momjian
Date:
Ron Johnson wrote:
> On Wed, 2003-09-03 at 09:50, Vivek Khera wrote:
> > >>>>> "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.
>
> It'll be better when domains have alterable constraints.  Your
> way is the traditional (and best, IMO) way, though.

In 7.4 we have:

    Add DOMAIN CHECK constraints (Rod)
    Add ALTER DOMAIN .. SET / DROP NOT NULL, SET / DROP DEFAULT, ADD / DROP
      CONSTRAINT (Rod)

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: What is the good equivalent for ENUM ?

From
Ron Johnson
Date:
On Wed, 2003-09-03 at 18:05, Bruce Momjian wrote:
> Ron Johnson wrote:
> > On Wed, 2003-09-03 at 09:50, Vivek Khera wrote:
> > > >>>>> "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.
> >
> > It'll be better when domains have alterable constraints.  Your
> > way is the traditional (and best, IMO) way, though.
>
> In 7.4 we have:
>
>     Add DOMAIN CHECK constraints (Rod)
>     Add ALTER DOMAIN .. SET / DROP NOT NULL, SET / DROP DEFAULT, ADD / DROP
>       CONSTRAINT (Rod)

"Rod"??

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

Regarding war zones: "There's nothing sacrosanct about a hotel
with a bunch of journalists in it."
Marine Lt. Gen. Bernard E. Trainor (Retired)

Re: What is the good equivalent for ENUM ?

From
"scott.marlowe"
Date:
On Wed, 3 Sep 2003, Ron Johnson wrote:

> On Wed, 2003-09-03 at 09:50, Vivek Khera wrote:
> > >>>>> "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.
>
> It'll be better when domains have alterable constraints.  Your
> way is the traditional (and best, IMO) way, though.

Speaking of enumeration, I noticed that the old copy of the SQL3 spec I
have lists an enumerated type.  Is that still in the SQL 3 spec, and if
so, are there any plans to implement it in pgsql?

Just wondering.

Re: What is the good equivalent for ENUM ?

From
Joseph Hepburne Healy
Date:

On Wed, 3 Sep 2003, Ron Johnson wrote:

> It'll be better when domains have alterable constraints.  Your
> way is the traditional (and best, IMO) way, though.

This is similar to what I have tended to use, but I have always wondered
about the efficency, and have used an int4 serial column as the primary
key and used this to reference the status data.

This has made it a little frustrating sometimes to write queries though.
Is referencing the varchar column generally reasonable in postgresql, and
not likely to give big performance issues as the tables get larger?
(sorry, this is a little hand wavy and not very exact, but I am wondering
if i am getting carried away giving everything an id)

This is the type of thing I have used: (edited from a couple of posts ago)

> > CREATE TABLE status_levels (
> >  status_levels_id serial primary key,
     status varchar(10)
> > ) 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 int4 NOT NULL DEFAULT 1 REFERENCES
status_levels(status_levels_id),
> >  ...
> > );
> >