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 [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
