Horton

Thread

  1. Migrating a MySQL schema with an enum

    Keith Gray <keith@heart.com.au> — 2002-03-20T23:41:30Z

    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

  2. Re: Migrating a MySQL schema with an enum

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-03-20T23:53:55Z

    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

  3. Re: Migrating a MySQL schema with an enum

    Peter Eisentraut <peter_e@gmx.net> — 2002-03-21T00:02:21Z

    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

  4. Re: Migrating a MySQL schema with an enum

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-03-21T02:31:12Z

    > 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

  5. Re: Migrating a MySQL schema with an enum

    Peter Eisentraut <peter_e@gmx.net> — 2002-03-21T04:50:48Z

    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

  6. Re: Migrating a MySQL schema with an enum

    Martin Marques <martin@bugs.unl.edu.ar> — 2002-03-21T11:31:44Z

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

  7. Re: Migrating a MySQL schema with an enum

    Martin Marques <martin@bugs.unl.edu.ar> — 2002-03-21T11:43:40Z

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

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

    Johny Jugianto <johny.q@rocketmail.com> — 2002-03-21T11:46:50Z

    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

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

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-03-21T14:29:39Z

    > 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

  10. Re: Migrating a MySQL schema with an enum

    Vincent Stoessel <vincent@xaymaca.com> — 2002-03-21T14:40:42Z

    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

  11. Re: Migrating a MySQL schema with an enum

    Ross Reedstrom <reedstrm@rice.edu> — 2002-03-21T16:02:28Z

    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

  12. Re: Migrating a MySQL schema with an enum

    Martin Marques <martin@bugs.unl.edu.ar> — 2002-03-22T11:18:23Z

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