A "PostgreSQL to Oracle converter" might be a really big project.
Having ported an application from PostgreSQL (7.3) to Oracle 9i, as I
recall, my biggest problems were:
- Quoting issues: the original PostgreSQL application quoted
integer/numeric type, and Oracle will not allow that, so I had to make sure
only types that had to be quoted, were. (I normally do not quote numbers
in PostgreSQL, but I inherited the application).
- Oracle not having OFFSET, LIMIT, so that you have to wrap SELECT in
another SELECT and use their virtual ROWNUM column, which gets (or did)
generated before an ORDER BY. That was a real pain: OFFSET and LIMIT are
_so_ useful for web applications, in particular (paging).
- Syntax for retrieving the next or current value from a Sequence is
different.
- Some reserved word issues (I believe we had a column called size, and
Oracle would not accept that, so we had to change the column name)
- Major issues with correct handling of UTF-8 data when inserting data in
multiple languages into the same database (most worked using Oracle char
set AL32UTF8, but some characters got messed up - never could figure that
out. Later versions of Oracle might work better.) It worked great in
PostgreSQL!
- Constraints: something about doing DROP TABLE CASCADE vs. Oracle DROP
TABLE CASCADE CONSTRAINTS.
- Temporary tables are completely different.
- At least 9i and earlier, no regular expression support in Oracle. I
believe there is now, but do not remember what, if any differences there
are.
- You cannot just throw in an "ANALYZE" in Oracle to get new statistics
after a bunch of Inserts, etc.
- I believe there was no BOOLEAN data type in Oracle 9i, so I had to
change those to another type.
- I remember there being some issues with the parsing of complex queries
and one db or the other wanting/not wanting an "AS" in there at various
places.
- I believe a bunch of the constraint syntax, view stuff, etc. in the
initial CREATE statements needed a lot of editing.
- No serial data type, so you had to write triggers or something to
auto-insert sequence values as a key.
- you had to say type is VARCHAR2 in Oracle to get the desired behavior
(instead of just VARCHAR)
- in Oracle, there is just one database, and multiple schemas. In
PostgreSQL 7.3, you could create multiple DBs, and separate sets of tables
that way. So, I think we had to use Oracle schemas for that (which you can
use now, in PostgreSQL, I believe).
Again, this was Oracle 9i, not 10g, etc. Your mileage may vary.
Oddly, I found almost nothing on the web about converting from PostgreSQL
to Oracle, but lots of mention of going the other direction!
Susan
Christopher
Kings-Lynne To: Chris Travers <chris@xxxxxxxxxxxxxxxxxxxxxxxx>
<chriskl@xxxxxxxxxxxxxx cc: Tom Lane <tgl@xxxxxxxxxxxxx>, Robert Treat <xzilla@xxxxxxxxxxxxxxxxxxxxx>,
om.au> pgsql-advocacy@xxxxxxxxxxxxxx, Scott Marlowe <smarlowe@xxxxxxxxxxxxxxxxx>, Greg
Sent by: Sabino Mullane <greg@xxxxxxxxxxxx>, pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [pgsql-advocacy] Oracle buys Innobase
pgsql-general-owner@pos |-------------------|
tgresql.org | [ ] Expand Groups |
|-------------------|
10/18/2005 10:10
PM
> 1) PostgreSQL to Oracle database conversion utilities released by
> Oracle (unlikely given extensible languages in PostgreSQL).
Strangely a pgsql to oracle exporter is a good thing. It'd be a great
feature of PostgreSQL. Imagine how many people would start on
PostgreSQL if they KNEW that one day they could easily move to Oracle if
they needed to. Risk management.
Chris
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
----------------------------------------------------------------------------------------------
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
