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.