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

Chuck,

> I have set up TABLES: clone_fasta, clone_qual and gb_accessions.  All
> three tables have the same PRIMARY KEY
> (project,plate,p_row,p_column,read,ver) but contain other unique data
> (seq and length in the case of TABLE clone_fasta shown below).

If I were setting up this database, I would create a "surrogate key"
using a SERIAL column and use that as my key for joins.  While your
"real" primary key is the 6-column combination above, I think you will
find 6-column joins a royal pain in practice.  In other words, change
the schema per my comments below.

> CREATE TABLE clone_fasta(
clone_id SERIAL NOT NULL,
> project INTEGER NOT NULL,
> plate INTEGER NOT NULL,          <-- to_char(plate, '000')
> p_row CHAR(1) NOT NULL,
> p_column INTEGER NOT NULL,  <-- to_char(p_column, '00')
> read CHAR(1) NOT NULL,
> ver INTEGER NOT NULL,
> length INTEGER NOT NULL,
> seq TEXT NOT NULL,
> PRIMARY KEY (project,plate,p_row,p_column,read,ver),
CONSTRAINT clone_id_cs UNIQUE (clone_id)
> );

If there are other unique/constrained columns, don't forget to add
constraints for them as well.  Finally, should any of the columns above
link to reference tables of possible values?  For example, I'm willing
to bet that the "read" column is constrained to 2 or 3 values.

And finally, you'll want the primary key and all columns with
constraints, or columns which regularly JOIN, indexed.  Pardon me if you
already know all this.

>      $result = $conn->exec(
>      "SELECT
> clone_fasta.seq,clone_fasta.length,clone_qual.qual,library.details,gb_accessions.accn_no
>      FROM clone_fasta,clone_qual,gb_accessions,library
>      WHERE clone_fasta.project = library.project AND
>      $la1 = $la2  AND  # set gb_accessions PK = clone_fasta PK
>      $la3 = $la2  AND  # set clone_qual PK = clone_fasta PK
>       clone_fasta.project = '$estIDs[0]' AND
>       clone_fasta.plate = '$estIDs[1]'   AND
>       clone_fasta.p_row = '$estIDs[2]'   AND
>       clone_fasta.p_column = '$estIDs[3]' AND
>       clone_fasta.read = '$estIDs[4]' AND
>       clone_fasta.ver = '$estIDs[5]'
>     ");
>
>
> I tried to use:  clone_fasta.to_char(plate,'000') = '$estIDs[1]' ,
> but this errors on syntax.

First, is that an interface language above that will swap out the
$estIDs[] values that you have listed?  PHP, maybe?

Second, yes, you did make a slight mistake in your syntax:
to_char(clone_fasta.plate,'000') = '$estIDs[1]'

Clearer, now?

BTW, I think that you're doing OK in the database design, but would
benefit significantly from a good database design book. See
http://techdocs.postgresql.org/bookreviews.php

-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