pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Migrating a MySQL schema with an enum
10+ messages / 7 participants
[nested] [flat]

* Migrating a MySQL schema with an enum
@ 2002-03-20 23:41 Keith Gray <keith@heart.com.au>
  2002-03-20 23:53 ` Re: Migrating a MySQL schema with an enum Bruce Momjian <pgman@candle.pha.pa.us>
  2002-03-21 00:02 ` Re: Migrating a MySQL schema with an enum Peter Eisentraut <peter_e@gmx.net>
  2002-03-21 11:31 ` Re: Migrating a MySQL schema with an enum Martín Marqués <martin@bugs.unl.edu.ar>
  0 siblings, 3 replies; 10+ messages in thread

From: Keith Gray @ 2002-03-20 23:41 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Migrating a MySQL schema with an enum
  2002-03-20 23:41 Migrating a MySQL schema with an enum Keith Gray <keith@heart.com.au>
@ 2002-03-20 23:53 ` Bruce Momjian <pgman@candle.pha.pa.us>
  2 siblings, 0 replies; 10+ messages in thread

From: Bruce Momjian @ 2002-03-20 23:53 UTC (permalink / raw)
  To: Keith Gray <keith@heart.com.au>; +Cc: pgsql-sql

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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Migrating a MySQL schema with an enum
  2002-03-20 23:41 Migrating a MySQL schema with an enum Keith Gray <keith@heart.com.au>
@ 2002-03-21 00:02 ` Peter Eisentraut <peter_e@gmx.net>
  2002-03-21 02:31   ` Re: Migrating a MySQL schema with an enum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2 siblings, 1 reply; 10+ messages in thread

From: Peter Eisentraut @ 2002-03-21 00:02 UTC (permalink / raw)
  To: Keith Gray <keith@heart.com.au>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Migrating a MySQL schema with an enum
  2002-03-20 23:41 Migrating a MySQL schema with an enum Keith Gray <keith@heart.com.au>
  2002-03-21 00:02 ` Re: Migrating a MySQL schema with an enum Peter Eisentraut <peter_e@gmx.net>
@ 2002-03-21 02:31   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2002-03-21 04:50     ` Re: Migrating a MySQL schema with an enum Peter Eisentraut <peter_e@gmx.net>
  2002-03-21 11:43     ` Re: Migrating a MySQL schema with an enum Martín Marqués <martin@bugs.unl.edu.ar>
  0 siblings, 2 replies; 10+ messages in thread

From: Christopher Kings-Lynne @ 2002-03-21 02:31 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; Keith Gray <keith@heart.com.au>; +Cc: pgsql-sql

> 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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Migrating a MySQL schema with an enum
  2002-03-20 23:41 Migrating a MySQL schema with an enum Keith Gray <keith@heart.com.au>
  2002-03-21 00:02 ` Re: Migrating a MySQL schema with an enum Peter Eisentraut <peter_e@gmx.net>
  2002-03-21 02:31   ` Re: Migrating a MySQL schema with an enum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-03-21 04:50     ` Peter Eisentraut <peter_e@gmx.net>
  1 sibling, 0 replies; 10+ messages in thread

From: Peter Eisentraut @ 2002-03-21 04:50 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Keith Gray <keith@heart.com.au>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Migrating a MySQL schema with an enum
  2002-03-20 23:41 Migrating a MySQL schema with an enum Keith Gray <keith@heart.com.au>
  2002-03-21 00:02 ` Re: Migrating a MySQL schema with an enum Peter Eisentraut <peter_e@gmx.net>
  2002-03-21 02:31   ` Re: Migrating a MySQL schema with an enum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-03-21 11:43     ` Martín Marqués <martin@bugs.unl.edu.ar>
  1 sibling, 0 replies; 10+ messages in thread

From: Martín Marqués @ 2002-03-21 11:43 UTC (permalink / raw)
  To: pgsql-sql

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
-----------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Migrating a MySQL schema with an enum
  2002-03-20 23:41 Migrating a MySQL schema with an enum Keith Gray <keith@heart.com.au>
@ 2002-03-21 11:31 ` Martín Marqués <martin@bugs.unl.edu.ar>
  2002-03-21 14:40   ` Re: Migrating a MySQL schema with an enum Vincent Stoessel <vincent@xaymaca.com>
  2002-03-21 16:02   ` Re: Migrating a MySQL schema with an enum Ross J. Reedstrom <reedstrm@rice.edu>
  2 siblings, 2 replies; 10+ messages in thread

From: Martín Marqués @ 2002-03-21 11:31 UTC (permalink / raw)
  To: pgsql-sql

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
-----------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Migrating a MySQL schema with an enum
  2002-03-20 23:41 Migrating a MySQL schema with an enum Keith Gray <keith@heart.com.au>
  2002-03-21 11:31 ` Re: Migrating a MySQL schema with an enum Martín Marqués <martin@bugs.unl.edu.ar>
@ 2002-03-21 14:40   ` Vincent Stoessel <vincent@xaymaca.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Vincent Stoessel @ 2002-03-21 14:40 UTC (permalink / raw)
  To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Migrating a MySQL schema with an enum
  2002-03-20 23:41 Migrating a MySQL schema with an enum Keith Gray <keith@heart.com.au>
  2002-03-21 11:31 ` Re: Migrating a MySQL schema with an enum Martín Marqués <martin@bugs.unl.edu.ar>
@ 2002-03-21 16:02   ` Ross J. Reedstrom <reedstrm@rice.edu>
  2002-03-22 11:18     ` Re: Migrating a MySQL schema with an enum Martín Marqués <martin@bugs.unl.edu.ar>
  1 sibling, 1 reply; 10+ messages in thread

From: Ross J. Reedstrom @ 2002-03-21 16:02 UTC (permalink / raw)
  To: Mart?n Marqu?s <martin@bugs.unl.edu.ar>; Vincent Stoessel <vincent@xaymaca.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Migrating a MySQL schema with an enum
  2002-03-20 23:41 Migrating a MySQL schema with an enum Keith Gray <keith@heart.com.au>
  2002-03-21 11:31 ` Re: Migrating a MySQL schema with an enum Martín Marqués <martin@bugs.unl.edu.ar>
  2002-03-21 16:02   ` Re: Migrating a MySQL schema with an enum Ross J. Reedstrom <reedstrm@rice.edu>
@ 2002-03-22 11:18     ` Martín Marqués <martin@bugs.unl.edu.ar>
  0 siblings, 0 replies; 10+ messages in thread

From: Martín Marqués @ 2002-03-22 11:18 UTC (permalink / raw)
  To: Ross J. Reedstrom <reedstrm@rice.edu>; Vincent Stoessel <vincent@xaymaca.com>; +Cc: pgsql-sql

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
-----------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2002-03-22 11:18 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-20 23:41 Migrating a MySQL schema with an enum Keith Gray <keith@heart.com.au>
2002-03-20 23:53 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-03-21 00:02 ` Peter Eisentraut <peter_e@gmx.net>
2002-03-21 02:31   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-03-21 04:50     ` Peter Eisentraut <peter_e@gmx.net>
2002-03-21 11:43     ` Martín Marqués <martin@bugs.unl.edu.ar>
2002-03-21 11:31 ` Martín Marqués <martin@bugs.unl.edu.ar>
2002-03-21 14:40   ` Vincent Stoessel <vincent@xaymaca.com>
2002-03-21 16:02   ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-03-22 11:18     ` Martín Marqués <martin@bugs.unl.edu.ar>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox