Обсуждение: Mysql -> PgSQL
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? RAY HUNTER
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' ...)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
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 before supporting the full standard ;-) Tommi
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 > before supporting the full standard ;-) Oh, yes, it is enum that is CHECK. 'set' has multiple values. It is a nifty MySQL feature, I must say. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
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. :)
> > 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 before supporting the full standard ;-) > > Oh, yes, it is enum that is CHECK. 'set' has multiple values. It is a > nifty MySQL feature, I must say. But not so tricky as it looks first. They use just a bitmask for it. The maximum number of allowed values is limited to 64 so it must be a 8-Byte bitmask. What do you think? Wouldn't it be nice to have this feature? It shouldn't be too hard to implement it. Tommi
Another question that comes to mind is: are there any plans to allow
user-defined types to accept argument lists? If that were the case, this
wouldn't be much of an issue, because anyone could just make a set type. As
it is, I think it needs to be added as a special case.
Regards,
Jeff
On Thursday 22 August 2002 10:54 pm, Tommi Maekitalo wrote:
> > > 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 before supporting the full standard ;-)
> >
> > Oh, yes, it is enum that is CHECK. 'set' has multiple values. It is a
> > nifty MySQL feature, I must say.
>
> But not so tricky as it looks first. They use just a bitmask for it. The
> maximum number of allowed values is limited to 64 so it must be a 8-Byte
> bitmask.
>
> What do you think? Wouldn't it be nice to have this feature? It shouldn't
> be too hard to implement it.
>
> Tommi
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Am Freitag, 23. August 2002 09:41 schrieb Jeff Davis:
> Another question that comes to mind is: are there any plans to allow
> user-defined types to accept argument lists? If that were the case, this
> wouldn't be much of an issue, because anyone could just make a set type. As
> it is, I think it needs to be added as a special case.
>
> Regards,
> Jeff
>
That brings me to an idea. You can write a function, which converts a list of
strings to a bitmask and stores this value. Then it should be possible to
write something like
insert into table(..., my_set) values (..., set('val1', 'val2', 'val3'))
as the original poster inteded to do. You need then a set of functions, for
queriing the values.
I'm not so familiar with udfs in postgresql. Is it possible to write udfs with
a variable number of arguments? Or is it possible to give a array-parameter
to a udf.
Mysqls set-type is still much more comfortable. Udfs is not a feature the
average Mysql-user wants to use.
Tommi
What the SQL standard wants you to do with SETs is to create another
table to hold the codes, and another table to hold the relationships
between the master table rows and the values.
We do have an array type that may work in such cases, though the array
type is ordered and doesn't have pre-defined values for the elements.
---------------------------------------------------------------------------
Tommi Maekitalo wrote:
> Am Freitag, 23. August 2002 09:41 schrieb Jeff Davis:
> > Another question that comes to mind is: are there any plans to allow
> > user-defined types to accept argument lists? If that were the case, this
> > wouldn't be much of an issue, because anyone could just make a set type. As
> > it is, I think it needs to be added as a special case.
> >
> > Regards,
> > Jeff
> >
>
> That brings me to an idea. You can write a function, which converts a list of
> strings to a bitmask and stores this value. Then it should be possible to
> write something like
>
> insert into table(..., my_set) values (..., set('val1', 'val2', 'val3'))
>
> as the original poster inteded to do. You need then a set of functions, for
> queriing the values.
>
> I'm not so familiar with udfs in postgresql. Is it possible to write udfs with
> a variable number of arguments? Or is it possible to give a array-parameter
> to a udf.
>
> Mysqls set-type is still much more comfortable. Udfs is not a feature the
> average Mysql-user wants to use.
>
>
> Tommi
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Yes, user defined functions can take arrays as arguments, and I think they can
take a variable number of arguments (after all, coalesce does).
And yes, MySQL's way is cleaner. I think the key is to allow user defined
types to take arguments, then all this stuff could go into a contrib module
and the functionality would be available with little effort.
Regards,
Jeff
On Friday 23 August 2002 01:25 am, Tommi Maekitalo wrote:
> Am Freitag, 23. August 2002 09:41 schrieb Jeff Davis:
> > Another question that comes to mind is: are there any plans to allow
> > user-defined types to accept argument lists? If that were the case, this
> > wouldn't be much of an issue, because anyone could just make a set type.
> > As it is, I think it needs to be added as a special case.
> >
> > Regards,
> > Jeff
>
> That brings me to an idea. You can write a function, which converts a list
> of strings to a bitmask and stores this value. Then it should be possible
> to write something like
>
> insert into table(..., my_set) values (..., set('val1', 'val2', 'val3'))
>
> as the original poster inteded to do. You need then a set of functions, for
> queriing the values.
>
> I'm not so familiar with udfs in postgresql. Is it possible to write udfs
> with a variable number of arguments? Or is it possible to give a
> array-parameter to a udf.
>
> Mysqls set-type is still much more comfortable. Udfs is not a feature the
> average Mysql-user wants to use.
>
>
> Tommi
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster