Re: converting Oracle scripts to PostgreSQL

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(at)illusions(dot)com