Re: Storing number '001' ?

Chuck,

Answer below.

> Sorry 'bout that, tried to shoot off the email prior to a meeting.
>
> The aim is to enter data into TABLE clone_contig.
>
> My plan was to first load TABLES clone and contig:
>
> CREATE TABLE clone (
> clone_id SERIAL PRIMARY KEY,
> project INTEGER NOT NULL,
> plate CHAR(3) NOT NULL,
> p_row CHAR(1) NOT NULL,
> p_column CHAR(2) NOT NULL,
> read CHAR(1) NOT NULL,
> ver INTEGER NOT NULL,
> seq TEXT NOT NULL,
> qual TEXT NOT NULL,
> UNIQUE (project,plate,p_row,p_column,read,ver)
> );
>
>
> CREATE TABLE contig (
> contig_id SERIAL PRIMARY KEY,
> assembly_date date NOT NULL,
> contig_no integer NOT NULL,
> ver integer NOT NULL,
> length INTEGER NOT NULL,
> seq TEXT NOT NULL,
> UNIQUE (assembly_date,contig_no,ver)
> );
>
>
>
> Next, to load TABLE clone_contig I was going to do the following:
>
>
> CREATE TABLE clone_contig(
> clone_id INTEGER REFERENCES clone(clone_id) ON DELETE CASCADE,
> contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE,
> UNIQUE(clone_id,contig_id)
> );
>
> Using Perl, I would build a hash of array containing an @clones for
> each contig:
>
> @{$HoC{$contig}{clones}}
>
> For each key(contig) I was going to query the DB for 'contig
> contig_contig_id_seq' from TABLE contig, AND 'clone_clone_id_seq'
> for each clone in the array @{$HoC{$contig}{clones}} from TABLE
> clone.
>
>
> So, if contig '20010822.123.1' was assembled from 2 clones
> (894001A01.x1, 963012H10.x1)
> (note: clone and contigs are represented in full form, not as
> parceled out in tables above for simplicity sake)
>
>
> TABLE clone TABLE contig
> clone_clone_id_seq contig_contig_id_seq
> 167756 894001A01.x1 <----->
> 37238238 20010822.123.1
> 21389 963012H10.x1 <----->
> 37238238 20010822.123.1
>
> SELECT clone_clone_id_seq FROM clone where clone='894001A01.x1';
> SELECT clone_clone_id_seq FROM clone where clone='963012H10.x1';
>
> SELECT contig_contig_id_seq FROM contig where
> contige='20010822.123.1';

Here's your only problem. You don't select "clone_clone_id_seq".
That's the name of a Sequence. You want to select your key column,
which is Clone_ID or Contig_ID.

If you actually wanted to get the ID of the last Contig you inserted,
for example, you could use the CURRVAL('contig_contig_id_seq') function.
See the following page for more info on how sequences work:

http://www.postgresql.org/idocs/index.php?sql-createsequence.html

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco