Re: Advice for using integer arrays?

On Tue, Jan 6, 2015 at 9:09 AM, Michael Heaney <mheaney(at)jcvi(dot)org> wrote:

> I'm fairly new to Postgres, and have a design issue for which an array of
> integers might be a good solution. But I'd like to hear from the experts
> before proceeding down this path.
>
> Essentially, I'm trying to model the relationship between a group of
> biological samples and their genes. Each sample (there are ~10K of them
> now, with more coming) will have about 30,000 genes. Conversely, a
> particular gene may be present in almost all samples.
>
> So I've created the following table to handle the many-to-many
> relationship:
>
> create table sample_gene (id serial, sample_id int, gene_id int);
>

What is the value of having this table at all? It doesn't seem to contain
anything informative, like an allele identifier, a resequence, or a copy
number variation. If you are just trying to record the fact that a gene
was present in that sample, perhaps it would be better to instead record
the genes have been deleted, rather than the ones that have not been
deleted? That would probably be a much smaller list.

> create table sample_gene_array (id serial, sample_id int, gene_id int []
> );
>
> So now the table data looks like this:
>
> sample_id | gene_id []
> ---------------------------------------
> 1 | [1:30475]
> 2 | [1:29973]
> etc.
>

I'm not familiar with the square bracket and colon as a syntax for
expressing int arrays. Are you taking liberties with the psql output, or
using a different client program? Does that represent the range from 1 to
30475, or the two values 1 and 30475?

Cheers,

Jeff