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

Re: Populating tables with data

From: Kumar S <ps_postgres(at)yahoo(dot)com>
To: Arthur van Dorp <arthur_vd(at)gmx(dot)net>
Cc: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Populating tables with data
Date: 2004-09-15 21:56:06
Message-ID: 20040915215606.21576.qmail@web61207.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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 

In response to

pgsql-novice by date

Next:From: Kumar SDate: 2004-09-16 15:41:05
Subject: A generic question: Schema without foreign key specification?
Previous:From: Devrim GUNDUZDate: 2004-09-15 21:44:29
Subject: Re: getting started

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