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, is it feasible to imagine one could run dumps from Postgre into Oracle and vice versa? Alternatively, run separate queries on both to set the structure and relations, then transfer only data?

PostgreSQL 7.3
Oracle 8.x 


Best regards,

Martin Matusiak







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, is it feasible to imagine 
one could run dumps from Postgre into Oracle and vice versa? Alternatively, run 
separate queries on both to set the structure and relations, then transfer only 
data?
 
PostgreSQL 7.3
Oracle 8.x 
 
 
Best regards,
 
Martin Matusiak
Mon, 17 Feb 2003 15:53:51 +0100
"Martin Matusiak" <db@juventuz.net>
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


17 Feb 2003 17:03:34 +0200
Hannu Krosing <hannu@tm.ee>
Re: postgresql and oracle, compatibility assessment
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
>
>
>



Tue, 18 Feb 2003 03:52:42 +0100
"Martin Matusiak" <db@juventuz.net>
Re: postgresql and oracle, compatibility assessment
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!"

Mon, 17 Feb 2003 22:15:15 -0500
Christopher Browne <cbbrowne@acm.org>
Re: postgresql and oracle, compatibility assessment
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


Tue, 18 Feb 2003 20:02:56 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: postgresql and oracle, compatibility assessment