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

Re: flat file database to postgres

From: Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>
To: postgresql(at)finner(dot)de
Cc: noor(at)cs(dot)man(dot)ac(dot)uk, pgsql-sql(at)postgresql(dot)org
Subject: Re: flat file database to postgres
Date: 2004-03-16 14:04:00
Message-ID: 20040316.230400.39151407.t-ishii@sra.co.jp (view raw or flat)
Thread:
Lists: pgsql-sql
> On Tue, 16 Mar 2004 12:33:57 -0000 (GMT) noor(at)cs(dot)man(dot)ac(dot)uk sat down, thought long and then wrote:
> 
> > 
> > Hi
> > 
> > Does anybody know how to export data from a flatfile (using perl) database
> > to postgres?
> > 
> > Thanks
> 
> If it is some kind of CSV (text with separators) you could use DBD::CSV for reading and DBD::Pg for
> writing. Or you read the file with Perl´s standard functions, treat the data to be
> PostgreSQL-insert-query-compatible (regarding apostrophes for example) and then use DBD::Pg (that´s
> what I usually do). If you have some kind of DBase files, you can use DBD::XBase.

I wrote a user defined C function which read a file and returns it.

test=# SELECT * FROM exttable('/etc/group', ':') AS g(gname TEXT, dummy TEXT, gid INTEGER, gmembers TEXT) LIMIT 10;
 gname  | dummy | gid |    gmembers     
--------+-------+-----+-----------------
 root   | x     |   0 | root
 bin    | x     |   1 | root,bin,daemon
 daemon | x     |   2 | root,bin,daemon
 sys    | x     |   3 | root,bin,adm
 adm    | x     |   4 | root,adm,daemon
 tty    | x     |   5 | 
 disk   | x     |   6 | root
 lp     | x     |   7 | daemon,lp
 mem    | x     |   8 | 
 kmem   | x     |   9 | 
(10 rows)

Of course this kind of function could be dangerous if you
thoughtlessly use it. Anyway I could post it if there's enough
interest.
--
Tatsuo Ishii

In response to

pgsql-sql by date

Next:From: Theodore PetroskyDate: 2004-03-16 14:31:07
Subject: Re: [SQL] could not create shared memory segment: Invalid argument
Previous:From: Luis P CaamanoDate: 2004-03-16 13:58:32
Subject: Can statement_timeout emulated NOWAIT?

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