pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMigrating 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>
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:53 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Keith Gray <keith@heart.com.au>
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-21 00:02 Peter Eisentraut <peter_e@gmx.net>
parent: Keith Gray <keith@heart.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-21 02:31 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Peter Eisentraut <peter_e@gmx.net>
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-21 04:50 Peter Eisentraut <peter_e@gmx.net>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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-21 11:31 Martín Marqués <martin@bugs.unl.edu.ar>
parent: Keith Gray <keith@heart.com.au>
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-21 11:43 Martín Marqués <martin@bugs.unl.edu.ar>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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-21 14:40 Vincent Stoessel <vincent@xaymaca.com>
parent: Martín Marqués <martin@bugs.unl.edu.ar>
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-21 16:02 Ross J. Reedstrom <reedstrm@rice.edu>
parent: 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-22 11:18 Martín Marqués <martin@bugs.unl.edu.ar>
parent: Ross J. Reedstrom <reedstrm@rice.edu>
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