Thread: converting Oracle scripts to PostgreSQL
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? Regards, Gary. ________________________________________________________________________________ This email (and any attachments) is private and confidential, and is intended solely for the addressee. If you have received this communication in error please remove it and inform us via telephone or email. Although we take all possible steps to ensure mail and attachments are free from malicious content, malware and virii, we cannot accept any responsibility whatsoever for any changes to content outwith our administrative bounds. The views represented within this mail are solely the view of the author and do not reflect the views of Graham Technology as a whole. ________________________________________________________________________________ Graham Technology plc http://www.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
Hi Gary, I went through a conversion from Oracle to PostgreSQL about 9 months ago. There's a little bit of documentation in the PostgreSQL manual http://www.postgresql.org/docs/7.4/interactive/plpgsql-porting.html or http://www.postgresql.org/docs/8.0/interactiveplpgsql-porting.html I had ment to submit docs to the manual but never got around to it since they could definately be expanded. The biggest issues I had for my project was that there were no statement level triggers and you couldn't do instead of triggers on views if my memory serves correct. plpgsql is really similar to Oracle plsql and in many cases you don't need to even change syntax. I could even you send the work I did since it was for school if you are interested. The reference manual for plpgsql can be found at: http://www.postgresql.org/docs/7.4/interactive/plpgsql.html or http://www.postgresql.org/docs/8.0/interactive/plpgsql.html Installing procedural languages: http://www.postgresql.org/docs/7.4/interactive/xplang.html or http://www.postgresql.org/docs/8.0/interactive/xplang.html triggers: http://www.postgresql.org/docs/7.4/interactive/triggers.html or http://www.postgresql.org/docs/8.0/interactive/triggers.html rules: http://www.postgresql.org/docs/7.4/interactive/rules.html or http://www.postgresql.org/docs/8.0/interactive/rules.html sql command syntax (for scripts): http://www.postgresql.org/docs/7.4/interactive/sql-commands.html or http://www.postgresql.org/docs/8.0/interactive/sql-commands.html Also try searching the mailing here: http://archives.postgresql.org/pgsql-sql/ You will probably find many of my old posts when I was converting Oracle to PostgreSQL. Hope this helps you get started. -Clint ----Original Message Follows---- From: "Gary Broadbent" <Gary.Broadbent@gtnet.com> Reply-To: <Gary.Broadbent@gtnet.com> To: <pgsql-sql@postgresql.org> Subject: [SQL] converting Oracle scripts to PostgreSQL Date: Wed, 19 Jan 2005 12:37:48 -0000 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? Regards, Gary. ________________________________________________________________________________ This email (and any attachments) is private and confidential, and is intended solely for the addressee. If you have received this communication in error please remove it and inform us via telephone or email. Although we take all possible steps to ensure mail and attachments are free from malicious content, malware and virii, we cannot accept any responsibility whatsoever for any changes to content outwith our administrative bounds. The views represented within this mail are solely the view of the author and do not reflect the views of Graham Technology as a whole. ________________________________________________________________________________ Graham Technology plc http://www.gtnet.com ________________________________________________________________________________ ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org