I am in the process of transitioning a bioinformatics database from
one schema to another, and I have to do some "massaging" of the data
in order to do it.
I have two tables, "gene" and "exon". Exon has a many-to-one
relationship with Gene. The structure of the Gene table isn't
important, but the Exon table looks like this:
CREATE TABLE exon(id SERIAL PRIMARY KEY,gene INTEGER REFERENCES gene(id),start INTEGER,stop INTEGER
);
Conceptually, all the exons for a given gene form a set, ordered by
their "start" attribute. I need to add a new integer column to the
table to store a counter for each exon that indicates their position
in this ordering.
Is there a straightforward way to populate this new position column?
I've done an iterative solution in PL/pgSQL which works (slowly), but
I was wondering if there was a more efficient way to do this kind of
thing.
Thanks in advance,
Christopher Maier