From: Dennis Sacks <dennis@illusions.com>
To: Gary.Broadbent@gtnet.com
Cc: pgsql-sql@postgresql.org
Subject: Re: converting Oracle scripts to PostgreSQL
Date: Tue, 25 Jan 2005 11:27:31 -0700
Message-ID: <41F68F93.6040009@illusions.com> (raw)
In-Reply-To: <CJEBKLMOCGBDDFFHIKGDIEDMCDAA.garyb@gtnet.com>
References: <CJEBKLMOCGBDDFFHIKGDIEDMCDAA.garyb@gtnet.com>
Gary Broadbent wrote:
>Hi,
>
>I have a database in Oracle that I want to convert to PostgreSQL.
>
>I have exported the scripts used to create the tables, constraints and
>sequences in Oracle and wish to convert these to postgreSQL scripts now.
>
>Is there an easy way to do this?
>
>
These aren't too hard (stored procedures are what can be a bit more
tricky). Here are some hints I've gotten in my notes. I'm sure there is
more:
data types -
number can be changed to decimal
date should be changed to timestamp (oracle's date has time,
postgresql's doesn't)
varchar2 needs to be changed to varchar
There may be optimizer hints for oracle that are part of the table
definitions - you'll need to remove those.
Sequences are pretty close. I think I had to get rid of "NOORDER" and
change "NOCYCLE" to "NO CYCLE" and add "MINVALUE 0" since the Oracle
sequence specified "START WITH 0" in this example:
Oracle:
CREATE SEQUENCE ABC_DEP_SEQ INCREMENT BY 1 START WITH 0 NOCYCLE CACHE 20
NOORDER;
Postgresql:
CREATE SEQUENCE ABC_DEP_SEQ INCREMENT BY 1 START WITH 0 MINVALUE 0 NO
CYCLE CACHE 20 ;
Queries:
Queries may need to be rewritten if they use the Oracle syntax for outer
joins. Also the NVL function can be replaced with coalesce and DECODE
will need to be rewritten with CASE.
SYSDATE can be replaced with NOW()
Also check out this doc for more hints:
http://www-2.cs.cmu.edu/~pmerson/docs/OracleToPostgres.pdf
Dennis Sacks
dennis@illusions.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dennis@illusions.com, Gary.Broadbent@gtnet.com
Subject: Re: converting Oracle scripts to PostgreSQL
In-Reply-To: <41F68F93.6040009@illusions.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox