Re: equivalent of mysql's SET type?

2011/3/11 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Thu, Mar 10, 2011 at 4:13 PM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
> wrote:
> > 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.
>
> actually composite types are fairly workable if you use table instead
> of a type (you can drop/add column, etc). in 9.1 you will be able to
> do this with vanilla composite type
> (http://developer.postgresql.org/pgdocs/postgres/sql-altertype.html)
>
Good news! Thanks for pointing that.

>
> in typical case I would agree that classic approach of separate
> relation is typically the way to go, there are exceptions -- for
> example enum gives you inline ordering -- or as in this case where OP
> is looking to simplify porting large body of application code.
>
Agree.

>
> merlin
>

--
// Dmitriy.