From: | Christopher Maier <maier(at)email(dot)unc(dot)edu> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | How can you generate a counter for ordered sets? |
Date: | 2007-05-17 13:19:48 |
Message-ID: | 4E9181C4-E46A-4D0F-8A35-1EE1C64AC48E@email.unc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma Jr | 2007-05-17 13:38:26 | Re: Whole-row comparison ? |
Previous Message | christian.roche.ext | 2007-05-17 12:56:08 | Whole-row comparison ? |