Re: How to convert Oracle database to Postgresql.

** Reply to message from Ron Johnson <ron.l.johnson@cox.net> on 18 Jun 2003
09:11:34 -0500
Yup,
 But then you need to reliably convert from quote, separated to tab delimited,
look after different representation of time and timestamp and null values (and
some other formatting issues which I can't remember. If youv'e got clobs/blobs
to transfer (or carriage returns in varchar fields (fine in db2))also, there is
just no good way to do it.
 So slower, but lots less labor intensive is ok for me.

Regards,
Wayne

> On Wed, 2003-06-18 at 07:49, Carlos Oliva wrote:
> > Hi Wayne,
> > Yes.  I would not mind getting a copy of the code.  I wrote a java
> > program to copy the data via JDBC and it works but it is rather slow.  I
> > am not using triggers either but I a use lots of blobs (I converted them
> > into bytea also)
> > Regards
> >
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wayne Armstrong
> > Sent: Wednesday, June 18, 2003 8:34 AM
> > To: Carlos; pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
> >
> >
> > ** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18
> > Jun 2003 08:12:00 -0400 Hi Carlos,  We've just been through this
> > recently.  We used db2look to get the ddl for the database, ran some
> > editing scripts over it (just ex) and fed that into postgres.  To load
> > data from the db2 database, I wrote a smalltalk program that for each
> > table, read from the db2 dtabase and inserted into the postgres
> > database. The smalltalk needs no prior knowledge of the table layouts to
> > do that- queries syscat.tables in the db2 database to get a list of
> > tables etc. You are welcome to the smalltalk executable (and source if
> > you want it (it is not really production code though :) )
> >
> > We used bytea in place of blob.
> > Triggers we did manually (we are not big users of triggers).
> >
> > All in all it was pretty easy. Places we came unstuck were on views
> > using the "with temp" syntax, and some recursive sql which we rewrote
> > using pgplsql and temp tables. Also Db2 will hapilly store 24:00:00 as
> > well as 00:00:00 for midnight. It's worth updateing any occurennces of
> > 24:00:00 to 00:00:00 before you convert.
>
> Wouldn't it be faster to do host-based conversions to tab-delimited
> files that can be loaded into postgres via COPY?
>
> --
> +-----------------------------------------------------------+
> | Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
> | Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
> |                                                           |
> | "Oh, great altar of passive entertainment, bestow upon me |
> |  thy discordant images at such speed as to render linear  |
> |  thought impossible" (Calvin, regarding TV)               |
> +-----------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html