Re: Mysql -> PgSQL

On Thu, 22 Aug 2002, Tommi Maekitalo wrote:

> Am Mittwoch, 21. August 2002 20:18 schrieb Bruce Momjian:
> > Ray Hunter wrote:
> > > I am trying to convert a data type in mysql to postgresql and was
> > > wondering which one i would use in pgsql.
> > >
> > > the type is "set" (example):
> > >
> > > name     set( 'val1', 'val2', 'val3' ) NOT NULL DEFAULT 'val2'
> > >
> > > How can i do that in pgsql?
> >
> > Sure:
> >
> >     x CHAR(4) NOT NULL DEFAULT 'val2' CHECK (x = 'val1' OR x = 'val2' ...)
>
> Thats not so trivial. The type set can have "zero or more values, each of
> which must be chosen from a list of allowed values" (from Mysql-manual). In
> PostgreSQL you have to use a details-table for this. This is a nonstandard
> feature of Mysql (these people concentrate in creating nonstandard extensions

Another way (which is alot more involved but I guess marginally closer to
the mysql one) would involve making a function that takes two arrays and
makes sure that each element of the first is a member of the second (and
if set doesn't allow duplicates, that as well) and then do an array as
the column with a check constraint using the function.

All in all a details table is better, though. :)