Re: How to convert Oracle database to Postgresql. - Mailing list pgsql-general

** 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.


Regards,
Wayne Armstrong,
Bacchus Management Systems.

> Is there a script or method to convert a DB2 database also?
> Thanks
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin Clift
> Sent: Wednesday, June 18, 2003 7:02 AM
> To: Mike Mascari
> Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold
> Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
>
>
> Hi Ursula,
>
> That script only works for Oracle 8.x though, and needs modification for
> Oracle 9i.
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> Mike Mascari wrote:
> > Ursula Lee wrote:
> >
> >
> >>Dear all,
> >>
> >>Is there any script/tool I can use to convert Oracle database to
> >>Postgresql?
> >
> >
> > The /contrib directory of the distribution contains an ora2pg script
> > used to generate a conversion. I've never used it though.
> >
> > Mike Mascari
> > mascarm@mascari.com
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> >       subscribe-nomail command to majordomo@postgresql.org so that
> your
> >       message can get through to the mailing list cleanly
>
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
> - Indira Gandhi
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html