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