Thread: postgresql and oracle, compatibility assessment

Martin Matusiak kirjutas E, 17.02.2003 kell 16:53:
> Greetings,
>  
> I am doing a project for college developing a java system utilizing a
> RDBMS. The choice is between PostgreSQL and Oracle and I'm wondering
> exactly how impossible would it be to make it compatible with both.
> Postgre is said to be completely ANSI SQL complaint,

PostgreSQL is *much* more ANSI comliant than Oracle.

> is it feasible to imagine one could run dumps from Postgre into Oracle
> and vice versa?

Not all data types are named the same (Oracles varchar is varchar2,
etc.)

> Alternatively, run separate queries on both to set the structure and
> relations, then transfer only data?

Much more likely to succeed.

You may be interested in OpenACS ( http://openacs.org/ ) project, which
runs on both Oracle and PostgreSQL.

---------
Hannu

Would it be possible to create some sort of transparent API based on ODBC to
be used with PostgreSQL and Oracle? I know there exists a JDBC - ODBC bridge
for java.


Martin

----- Original Message -----
From: "Hannu Krosing" <hannu@tm.ee>
To: "Martin Matusiak" <db@juventuz.net>
Cc: <pgsql-hackers@postgresql.org>
Sent: Monday, February 17, 2003 4:03 PM
Subject: Re: [HACKERS] postgresql and oracle, compatibility assessment


> Martin Matusiak kirjutas E, 17.02.2003 kell 16:53:
> > Greetings,
> >
> > I am doing a project for college developing a java system utilizing a
> > RDBMS. The choice is between PostgreSQL and Oracle and I'm wondering
> > exactly how impossible would it be to make it compatible with both.
> > Postgre is said to be completely ANSI SQL complaint,
>
> PostgreSQL is *much* more ANSI comliant than Oracle.
>
> > is it feasible to imagine one could run dumps from Postgre into Oracle
> > and vice versa?
>
> Not all data types are named the same (Oracles varchar is varchar2,
> etc.)
>
> > Alternatively, run separate queries on both to set the structure and
> > relations, then transfer only data?
>
> Much more likely to succeed.
>
> You may be interested in OpenACS ( http://openacs.org/ ) project, which
> runs on both Oracle and PostgreSQL.
>
> ---------
> Hannu
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>

db@juventuz.net ("Martin Matusiak") wrote:
> Would it be possible to create some sort of transparent API based on
> ODBC to be used with PostgreSQL and Oracle? I know there exists a
> JDBC - ODBC bridge for java.

If you wrote your application exclusively using JDBC using functions
existing in both PostgreSQL and Oracle, you'd certainly have a "lowest
common denominator" that would satisfy this requirement.

You would be taking advantage neither of PostgreSQL's strengths nor of
Oracle's strengths, which is considerably wasteful on both sides of
the coin.

You would have to design your application so that it does not use
either system's stored procedure systems, because they are not totally
interoperable.  

You would have to ensure that your access patterns were concurrently
compatible with Oracle rollback buffers and PostgreSQL MVCC.

At the end of all this, you more than likely have an application that
will perform questionably in both places...
-- 
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www3.sympatico.ca/cbbrowne/lsf.html
Would-be National Mottos:
USA: "There oughta' be a law!"

Martin Matusiak writes:

> I am doing a project for college developing a java system utilizing a
> RDBMS. The choice is between PostgreSQL and Oracle and I'm wondering
> exactly how impossible would it be to make it compatible with both.

I tried to do something like that recently and the way Oracle handles
many perfectly innocent SQL commands is completely craptacular.  Don't try
it, especially with Oracle 8.  (9 is better in some regards.)

-- 
Peter Eisentraut   peter_e@gmx.net