Re: COPY command, linking foreign keys

Table: gene_tab

 gene_id | gene_name
---------+-----------
       1 | AARS
       2 | AGC1
       3 | APOA5
       4 | APOB
       5 | ATP13A2
       6 | C9orf106
       7 | CCNI
       8 | CENTG3
       9 | CITED4
      10 | GPR24

Table: gene_exp

 gene_id | gene_exp
---------+----------
(0 rows)



Table : gene_exp_temp

 temp_name | temp_exp
-----------+----------
 AARS      |      100
 AGC1      |      200
 APOA5     |      201
 APOB      |      202


question : Inserting into gene_exp from gene_exp_temp:


insert into gene_exp (gene_id, gene_exp) values (
(select gene_id from gene_tab, gene_exp_temp where
gene_name = temp_name),
(selct temp_exp from gene_exp_temp, gene_tab where
temp_exp = gene_id));

Something is wrong somewhere. I know I am not still
matured enough in terms of linking data.  Could you
help me where the problem is?

Thanks Sean.

-Srini






> > There are two questions :
> >
> > 1. In table 'exp' I have gene_no and not
> gene_name. In
> > the tab delim file I have gene_name.  So, what is
> the
> > ideal way to link these up - a fast one after
> > 'COPY'ing this tab delim file in to exp_table.
> should
> > I include gene_name also into the 'exp' table or
> ask
> > postgres to link up with 'Gene' table through
> > 'gene_no' foreign key.
> > 2. Can this be done by simple SQL statement or
> should
> > I have to write a pl/pgql script.
> >
> > would any one please help me in teaching to get
> around
> > this problem.
>
> Srini,
>
> One way to do this that works well for me is to
> "copy" the data into a
> temporary table and then use regular SQL to do the
> inserts into separate
> tables.  This technique is quite fast.
>
> Sean
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map
> settings
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com