Re: Storing number '001' ? - Mailing list pgsql-novice

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@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco