Re: Populating tables with data

Thank you Arthur for your e-mail.

I did not face the realistic problem until I tried to
populate the tables.

In fact I am developing this database to store
microarray data(gene chip data).

the whole data is distributed in 3 kinds of TAB
delimtted text.

Each file will have 45K rows and the first approx. 40
rows is normal text data without tab delimitation.
Then onwards the text is in 4 different columns.

The problem with copy was :
1. Columns in Tables did not match the number of
columns in the flat file.
2. Each column in Flat file has different location in
different tables (although this is not a problem due
to
difference in number of columns it raises and error).
3. The foreign keys in tables are defined as SERIAL
type and COPY does not happen becuase of duplication
of keys.

I cannot do 45 K insert statements for the contents of
files.

I have two questions (stumbling blocks) in order to
proceed further and I am stuck:

My collegue suggested me to change the schema to a
3-order normalized schema because the existing schema
is a 1-order normalized (I hope I am conveying the
correct terminology). That means, I define entities
exactly like the flat files and define more
Relationship tables instead of foreign keys. This also
makes indexing and querying more easy.

1. Is there a solution for pouplating data instead of
COPY tool without changing schema.
2. If I change schema will populating data tables will
be made easy.

Could any one please help me.

Thank you.

Kumar

--- Arthur van Dorp <arthur_vd(at)gmx(dot)net> wrote

> Hi
>
> > To make my question short, I have a (say) 3
> tables. My data is in tab
> > delimted text. I have to parse the data and direct
> the contents of
> > each colums to columns in each table.
> >
> > How can I do this using Python? Any ideas please.
>
> You don't even have to use Python. The COPY command
> should be enough:
>
http://www.postgresql.org/docs/7.4/interactive/sql-copy.html
>
> Arthur
>

__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail