Re: equivalent of mysql's SET type?

Reece Hart wrote:
> On Tue, Mar 8, 2011 at 5:52 PM, Darren Duncan <darren@darrenduncan.net>wrote:
>
>> Try starting with an enum type to define the possible values:
>>
>>  CREATE TYPE Consequence_Type
>>    AS ENUM ('ESSENTIAL_SPLICE_SITE','STOP_GAINED',
>>      'STOP_LOST','COMPLEX_INDEL','SPLICE_SITE');
>>
>> ... and then you could try using "ARRAY OF Consequence_Type" or some such.
>
> Arrays occurred to me, but they don't give a set (i.e., a consequence type
> can appear more than once) unless I write the code to dedupe the array.
> However, if I were going to put that much effort into it, I might as well
> represent the set directly and obviate the dedupe. Or, am I missing
> something from your suggestion?

You also have another option, which is to split consequence_type into
a second table, and have a record in there for each value in the set.  You would
maintain a lack of duplicates by having an ordinary unique/primary key
constraint on the second table.  This second-table approach is logically
equivalent to your use of a set-valued field. -- Darren Duncan