Message-ID: <41F68F93.6040009@illusions.com>
Date: Tue, 25 Jan 2005 11:27:31 -0700
From: Dennis Sacks <dennis@illusions.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: Gary.Broadbent@gtnet.com
Cc: pgsql-sql@postgresql.org
Subject: Re: converting Oracle scripts to PostgreSQL
References: <CJEBKLMOCGBDDFFHIKGDIEDMCDAA.garyb@gtnet.com>
In-Reply-To: <CJEBKLMOCGBDDFFHIKGDIEDMCDAA.garyb@gtnet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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