Skip site navigation (1) Skip section navigation (2)

Re: Storing number '001' ?

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: Charles Hauser <chauser(at)acpub(dot)duke(dot)edu>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Storing number '001' ?
Date: 2001-12-17 19:50:02
Message-ID: web-530374@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: 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(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

pgsql-novice by date

Next:From: Josh BerkusDate: 2001-12-17 23:36:06
Subject: Re: Storing number '001' ?
Previous:From: Bruce MomjianDate: 2001-12-17 17:04:07
Subject: Re: Numbering Rows (SEQUENCE, OID) questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group