Thread: Migrating a MySQL schema with an enum

Migrating a MySQL schema with an enum

From
Keith Gray
Date:
SQL gurus,

Migrating a MySQL schema with an enum to PostgreSQL, 
is there an equivalent type or do I have to declare 
another table for "enum" values.

-- 
Keith Gray

Technical Development Manager
Heart Consulting Services P/L
mailto:keith@heart.com.au


Re: Migrating a MySQL schema with an enum

From
Bruce Momjian
Date:
Keith Gray wrote:
> SQL gurus,
> 
> Migrating a MySQL schema with an enum to PostgreSQL, 
> is there an equivalent type or do I have to declare 
> another table for "enum" values.

You can use another table, or use CHECK constraints to limit the
possible values.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Migrating a MySQL schema with an enum

From
Peter Eisentraut
Date:
Keith Gray writes:

> Migrating a MySQL schema with an enum to PostgreSQL,
> is there an equivalent type or do I have to declare
> another table for "enum" values.

Generally, you'd use a text field with a check constraint.  For example:

CREATE TABLE car (...color text check (color in ('blue', 'green', 'yellow')),...
);

-- 
Peter Eisentraut   peter_e@gmx.net



Re: Migrating a MySQL schema with an enum

From
"Christopher Kings-Lynne"
Date:
> Keith Gray writes:
>
> > Migrating a MySQL schema with an enum to PostgreSQL,
> > is there an equivalent type or do I have to declare
> > another table for "enum" values.
>
> Generally, you'd use a text field with a check constraint.  For example:
>
> CREATE TABLE car (
>  ...
>  color text check (color in ('blue', 'green', 'yellow')),
>  ...
> );

Nope - cos you need 'blue,green' as a value, etc. as well.

I use a varbit field and do the translations from an array of strings to a
bitset and vice versa in the app layer.

Chris



Re: Migrating a MySQL schema with an enum

From
Peter Eisentraut
Date:
Christopher Kings-Lynne writes:

> > Generally, you'd use a text field with a check constraint.  For example:
> >
> > CREATE TABLE car (
> >  ...
> >  color text check (color in ('blue', 'green', 'yellow')),
> >  ...
> > );
>
> Nope - cos you need 'blue,green' as a value, etc. as well.

That's not an enumeration type, that's a set.  For set's you create a
separate table.

-- 
Peter Eisentraut   peter_e@gmx.net



Re: Migrating a MySQL schema with an enum

From
Martín Marqués
Date:
On Mié 20 Mar 2002 20:41, you wrote:
> SQL gurus,
>
> Migrating a MySQL schema with an enum to PostgreSQL,
> is there an equivalent type or do I have to declare
> another table for "enum" values.

I had to migrate a DB from MySQL to PostgreSQL that had some enum as field 
data type (I can't believe that something like that exists), and because it 
was something like:

enum('s','n')

I changed it for:

col1 CHAR CHECK (col1 IN ('s','n'))

Which is SQL. :-)

If what is enumerated are numbres, just use INT instead of CHAR.

Saludos... :-)

-- 
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica                      Universidad Nacional
        del Litoral
 
-----------------------------------------------------------------


Re: Migrating a MySQL schema with an enum

From
Martín Marqués
Date:
On Mié 20 Mar 2002 23:31, you wrote:
> > Keith Gray writes:
> > > Migrating a MySQL schema with an enum to PostgreSQL,
> > > is there an equivalent type or do I have to declare
> > > another table for "enum" values.
> >
> > Generally, you'd use a text field with a check constraint.  For example:
> >
> > CREATE TABLE car (
> >  ...
> >  color text check (color in ('blue', 'green', 'yellow')),
> >  ...
> > );
>
> Nope - cos you need 'blue,green' as a value, etc. as well.

That's not how enum() works.

-- 
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica                      Universidad Nacional
        del Litoral
 
-----------------------------------------------------------------


[OOT] timestamp / time ('now') in postgresql 7.2?

From
"Johny Jugianto"
Date:
hi

i'm have installed latest version of postgresql which is 7.2
why i can not use timestamp('now') or time('now') ?
it said : ERROR:    parser: parse error at or near "'"

can anyone explain why?
i'm try in 7.1.x. it's works well
did i miss something when installation? is it in new version need somekind
of "plug-in"?

ps: date('now') is work fine

thx in advance



Re: [OOT] timestamp / time ('now') in postgresql 7.2?

From
Christopher Kings-Lynne
Date:
> i'm have installed latest version of postgresql which is 7.2
> why i can not use timestamp('now') or time('now') ?
> it said : ERROR:    parser: parse error at or near "'"

Those two words have become a little more reserved.  If you go
"timestamp"('now') or "time"('now') it should work.

Even better, I think you can go timestamp 'now' or time 'now' instead
IIRC.

Chris



Re: Migrating a MySQL schema with an enum

From
Vincent Stoessel
Date:
Martín Marqués wrote:
> On Mié 20 Mar 2002 20:41, you wrote:
> 
>>SQL gurus,
>>
>>Migrating a MySQL schema with an enum to PostgreSQL,
>>is there an equivalent type or do I have to declare
>>another table for "enum" values.
> 
> 
> I had to migrate a DB from MySQL to PostgreSQL that had some enum as field 
> data type (I can't believe that something like that exists)

<snip>

another shot at the mysql db.
Please don't forget that PG also has non standard data types
available.


Vinny




Re: Migrating a MySQL schema with an enum

From
"Ross J. Reedstrom"
Date:
On Thu, Mar 21, 2002 at 09:40:42AM -0500, Vincent Stoessel wrote:
> Mart?n Marqu?s wrote:
> >On Mi? 20 Mar 2002 20:41, you wrote:
> >
> >>SQL gurus,
> >>
> >>Migrating a MySQL schema with an enum to PostgreSQL,
> >>is there an equivalent type or do I have to declare
> >>another table for "enum" values.
> >
> >
> >I had to migrate a DB from MySQL to PostgreSQL that had some enum as field 
> >data type (I can't believe that something like that exists)
> 
> <snip>
> 
> another shot at the mysql db.
> Please don't forget that PG also has non standard data types
> available.

Your pique at seeing what you took as an unfair shot at another db caused you
to be blinded to what Martin was pointing out. In this case, MySQL has a
gratutiously non-standard feature: the equivalent CHECK constraint is only
a few more characters, and easier to read (IMHO)

Ross


Re: Migrating a MySQL schema with an enum

From
Martín Marqués
Date:
On Jue 21 Mar 2002 13:02, Ross J. Reedstrom wrote:
> On Thu, Mar 21, 2002 at 09:40:42AM -0500, Vincent Stoessel wrote:
> > Mart?n Marqu?s wrote:
> > >On Mi? 20 Mar 2002 20:41, you wrote:
> > >>SQL gurus,
> > >>
> > >>Migrating a MySQL schema with an enum to PostgreSQL,
> > >>is there an equivalent type or do I have to declare
> > >>another table for "enum" values.
> > >
> > >I had to migrate a DB from MySQL to PostgreSQL that had some enum as
> > > field data type (I can't believe that something like that exists)
> >
> > <snip>
> >
> > another shot at the mysql db.
> > Please don't forget that PG also has non standard data types
> > available.
>
> Your pique at seeing what you took as an unfair shot at another db caused
> you to be blinded to what Martin was pointing out. In this case, MySQL has
> a gratutiously non-standard feature: the equivalent CHECK constraint is
> only a few more characters, and easier to read (IMHO)

Thanks Ross, I was just about to say the same thing. Why had an unstandard 
data type, whn you can have it SQL-standard.
I think that the problem MySQL has is CHECK.

Saludos... :-)

-- 
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica                      Universidad Nacional
        del Litoral
 
-----------------------------------------------------------------