Mysql -> PgSQL

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

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