PostgresOracleInteroperability - Tech

_(back to TechNotes)_

In general I like to use Postgresql for development because it's light, quick, and the tools are very developer friendly.  It's not really suited for large-scale deployment, though, so often I'll end up having to deploy code that I've developed on Postgres to an Oracle database for production.  Here are some "gotchas" that I've run into (please let me know if you are aware of others).

Postgres allows =character varying= without an explicit length.  Oracle does not, so you should write e.g. =character varying(255)= .

Oracle seems to have many more reserved words that postgres so check frequently.

If you define a table and put quotation marks around it Postgres seems to ignore the quotes.  Oracle doesn't, so for example you could have a table named =foo= and a table named ="foo"= and they would be two different tables.  Unfortunately, Postgres appears to wrap table names in double-quotes when you dump a database.  Hint:  <code>sed 's/"\(\w*\)"/\1/'</code> will filter out the quotes (but you may need to run it over the file a few times since it only filters one pair per pass).

Postgres is very loose about foreign keys - it doesn't require that a foreign key actually reference an existing key.  Oracle does, so you need to make sure that there's a key for every foreign key.

Oracle doesn't like the string ='now'= as a date, so if you're using JDBC it's probably better to use a bind parameter.

---+++ Tools

http://www.samse.fr/GPL/ora2pg/ - ora2pg is a perl script to convert Oracle schemas to postgresql databases.

-- Main.TobyCabot - 21 Sep 2001 - 16 Jul 2002 <br>