Re: equivalent of mysql's SET type?

2011/3/9 John R Pierce <pierce(at)hogranch(dot)com>

> On 03/08/11 5:06 PM, Reece Hart wrote:
>
>> I'm considering porting a MySQL database to PostgreSQL. That database uses
>> MySQL's SET type. Does anyone have advice about representing this type in
>> PostgreSQL?
>>
>> MySQL DDL excerpt:
>> CREATE TABLE `transcript_variation` (
>> `transcript_variation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
>> `transcript_stable_id` varchar(128) NOT NULL,
>> ...
>> `consequence_type`
>> set('ESSENTIAL_SPLICE_SITE','STOP_GAINED','STOP_LOST','COMPLEX_INDEL','SPLICE_SITE')
>> ) ENGINE=MyISAM AUTO_INCREMENT=174923212 DEFAULT CHARSET=latin1;
>>
>>
>>
> why not just have a set of booleans in the table for these individual
> on/off attributes? wouldn't that be simplest?

Yes, it might be simplest at first sight.
But classical solution is relation N - N scales simpler than
any tricks with bytes.
Unfortunately, enums and composite types are not extensible. And
if you need to add yet another option (or remove some option) it
will be problematic.
In case of N - N relation you need just use INSERT/DELETE.

>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.