Ladies and Gentemen,

in a database project I ported an Oracle database definition to
PostgreSQL 7.3.2 as an aside. During this process I found a 
couple of incompatibilities in the SQL dialects of both DBMS.

I compiled the following list for whatever it might be good for -
for example to transscribe Oracle DD statements to PostgreSQL DD
statements. Or to enhance PostgreSQL to increase Oracle
compatibility - as far as this might be sensible and desirable.
Or to establish a more comprehensive Oracle/PostgreSQL list.

Since this is nothing I am actively working on I don't expect any
response. However, if it should be useful for you, I'd appreciate
some feedback.

   + At least the following names are a) different and b)
     PostgreSQL does not understand the Oracle equivalent:

     Oracle:  PostgreSQL:
     VARCHAR2 varchar
     NUMBER   int
     CLOB     bytea
     sysdate  current_date

   + CREATE SCHEMA: Sometimes a schema created in PostgreSQL
     disappears if there is nothing in it.

   + CREATE INDEX: PostgreSQL should allow specifying a namespace
     for the index, even if the namespace is required to be the
     same as the parent table. This would increase Oracle
     compatibility.

   + CREATE SEQUENCE: Oracle allows (or requires) "INCREMENT BY"
     instead of just "INCREMENT". Same for "START WITH" vs.
     "START". Oracle allows explicit NOCYCLE and NOCACHE. It also
     has a keyword ORDER.

   + Indexes and table constraints share the same namespace.

   + Oracle's DISABLE in foreign key specification in table
     constraint is unknown by PostgreSQL.

   + PostgreSQL does not support Oracle's CREATE PUBLIC SYNONYM

   + PostgreSQL does not support the NUMBER keyword without (...)
     i.e. something in parenthesis following it.

   + Oracle's SEQ_KATALOGID.nextval should be translated to
     nextval('SEQ_KATALOGID').


                           Rainer Klute IT-Consulting GmbH
  Dipl.-Inform.
  Rainer Klute             E-Mail:  klute@rainer-klute.de
  Körner Grund 24          Telefon: +49 172 2324824
D-44143 Dortmund           Telefax: +49 231 5349423

Thu, 2 Oct 2003 08:37:12 +0200
Rainer Klute <rainer.klute@epost.de>
Oracle/PostgreSQL incompatibilities
>    + CREATE SCHEMA: Sometimes a schema created in PostgreSQL
>      disappears if there is nothing in it.

If true, this would be a bug.  Do you have a reproducible test case?

>    + CREATE INDEX: PostgreSQL should allow specifying a namespace
>      for the index, even if the namespace is required to be the
>      same as the parent table. This would increase Oracle
>      compatibility.

Agreed for 7.5.

>    + CREATE SEQUENCE: Oracle allows (or requires) "INCREMENT BY"
>      instead of just "INCREMENT". Same for "START WITH" vs.
>      "START". Oracle allows explicit NOCYCLE and NOCACHE. It also
>      has a keyword ORDER.

7.4 implements the 200N proposed sequence names -- which are nearly
equivalent to the Oracle definition (I believe RESTART WITH is missing 
in Oracle).

>    + Indexes and table constraints share the same namespace.

Well.. some constraints are implemented via indexes.  The index doesn't
conflict with the constraint name, it conflicts with the index name --
but they share the same name.

Anyway, the way to do this is better hide the implementation of a unique
or primary key constraint. Or allow for empty, invalid or missing names
in those cases. For example, constraint index names could be the OID of
the constraint.  Since a fully numerical name is invalid, this would
effectively remove the problem.

>    + PostgreSQL does not support the NUMBER keyword without (...)
>      i.e. something in parenthesis following it.

From what I can tell, PostgreSQL doesn't support NUMBER at all. Numeric
is the SQL specified version.

dev_iqdb=# select 5::numeric;
 numeric
---------
       5
(1 row)

>    + Oracle's SEQ_KATALOGID.nextval should be translated to
>      nextval('SEQ_KATALOGID').

nextval('') has dependency tracking issues, so needs to be changed.

The debate is whether to support Oracle or DB2 syntax for next value of
indexes.  Oracle syntax is more common, DB2 syntax is in the SQL 200N
proposal.
Fri, 03 Oct 2003 11:35:28 -0400
Rod Taylor <rbt@rbt.ca>
Re: Oracle/PostgreSQL incompatibilities
Rainer Klute <rainer.klute@epost.de> writes:
> [ some good comments, but a few things I want to respond to ]

>    + CREATE SCHEMA: Sometimes a schema created in PostgreSQL
>      disappears if there is nothing in it.

This is more than a bit hard to believe.  Can you give an example?

>    + CREATE SEQUENCE: Oracle allows (or requires) "INCREMENT BY"
>      instead of just "INCREMENT". Same for "START WITH" vs.
>      "START". Oracle allows explicit NOCYCLE and NOCACHE. It also
>      has a keyword ORDER.

It looks like much of this has been done as of 7.4.  I dunno what ORDER
is for though.

>    + PostgreSQL does not support the NUMBER keyword without (...)
>      i.e. something in parenthesis following it.

Don't follow this one either.  We don't have NUMBER --- are you speaking
of NUMERIC?  If so, I'm not aware of any context where you're required
to put a precision on NUMERIC.  Again, may we see an example?

			regards, tom lane

Fri, 03 Oct 2003 11:53:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Oracle/PostgreSQL incompatibilities 

>>   + CREATE SCHEMA: Sometimes a schema created in PostgreSQL
>>     disappears if there is nothing in it.
>>    
>>
>
>This is more than a bit hard to believe.  Can you give an example?
>  
>

We use schema's ALOT in our applications. I have yet to see this happen.


>>   + PostgreSQL does not support the NUMBER keyword without (...)
>>     i.e. something in parenthesis following it.
>>    
>>
>
>Don't follow this one either.  We don't have NUMBER --- are you speaking
>of NUMERIC?  If so, I'm not aware of any context where you're required
>to put a precision on NUMERIC.  Again, may we see an example?
>  
>

Ditto.

Sincerely,

Joshua D. Drake



>			regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>  
>

-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
Postgresql support, programming, shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org



Fri, 03 Oct 2003 09:12:58 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: Oracle/PostgreSQL incompatibilities
I think he means that you can do this in oracle :

CREATE TABLE test (id NUMBER);

Oracle treats NUMBER as NUMBER(40) I think.
This seems to be an example of Oracle making up standards as they go 
along - do we want to copy this sort of thing ?

I usually just run a substitution of NUMBER(..) -> NUMERIC(..) and 
NUMBER -> INTEGER when transporting schemas from Oracle to Pg.
(This needs a little care - as NUMBER in Oracle has bigger scale than 
INTEGER in Pg)

regards

Mark



>
>>>   + PostgreSQL does not support the NUMBER keyword without (...)
>>>     i.e. something in parenthesis following it.
>>>   
>>
>>
>> Don't follow this one either.  We don't have NUMBER --- are you speaking
>> of NUMERIC?  If so, I'm not aware of any context where you're required
>> to put a precision on NUMERIC.  Again, may we see an example?
>>  
>>
>
> Ditto.
>
> Sincerely,
>
> Joshua D. Drake
>
>
>
>>             regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faqs/FAQ.html
>>  
>>
>


Sat, 04 Oct 2003 15:26:36 +1200
Mark Kirkwood <markir@paradise.net.nz>
Re: Oracle/PostgreSQL incompatibilities
Finger or brain trouble, here is the correction :

 >NUMBER -> INTEGER when transporting schemas from Oracle to Pg.
 >(This needs a little care - as NUMBER in Oracle has bigger *precision* 
than INTEGER in Pg)

Thinking about this a bit more, its probably fine to just substitute 
NUMERIC for NUMBER, but obviously INTEGER is going to perform better if 
it can be used.

regards

Mark



Sat, 04 Oct 2003 18:50:03 +1200
Mark Kirkwood <markir@paradise.net.nz>
Re: Oracle/PostgreSQL incompatibilities
On Fri, 03 Oct 2003 11:53:05 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rainer Klute <rainer.klute@epost.de> writes:
> > [ some good comments, but a few things I want to respond to ]
> 
> >    + CREATE SCHEMA: Sometimes a schema created in PostgreSQL
> >      disappears if there is nothing in it.
> 
> This is more than a bit hard to believe.  Can you give an example?

Well, no. I had to struggle a bit with schemas until I had it running the way I wanted. But now I can't reproduce the problem. You may take that as good news. :-)


> >    + CREATE SEQUENCE: Oracle allows (or requires) "INCREMENT BY"
> >      instead of just "INCREMENT". Same for "START WITH" vs.
> >      "START". Oracle allows explicit NOCYCLE and NOCACHE. It also
> >      has a keyword ORDER.
> 
> It looks like much of this has been done as of 7.4.  I dunno what ORDER
> is for though.

I don't know either but at least here's an example:
CREATE SEQUENCE ADAPTER.SEQ_ADAPTERSTORE INCREMENT BY 1 START WITH 1 MAXVALUE 999999999999999 NOCYCLE NOCACHE ORDER;


> >    + PostgreSQL does not support the NUMBER keyword without (...)
> >      i.e. something in parenthesis following it.
> 
> Don't follow this one either.  We don't have NUMBER --- are you speaking
> of NUMERIC?  If so, I'm not aware of any context where you're required
> to put a precision on NUMERIC.  Again, may we see an example?

Here's an Oracle example:

create table BUSINESS_PROCESS
        (ID NUMBER not null primary key,
         BUSINESS_PROTOCOL varchar2(254),
         PROTOCOL_VERSION varchar2(254),
         DEFAULT_CONVERSATION_TIMEOUT NUMBER);


Best regards
Rainer Klute

  Rainer Klute             E-Mail:  rainer.klute@epost.de
  Körner Grund 24          Telefon: (0231) 511693
D-44143 Dortmund           Mobil:   (0172) 2324824

Sun, 5 Oct 2003 07:30:55 +0200
Rainer Klute <rainer.klute@epost.de>
Re: Oracle/PostgreSQL incompatibilities
Rainer Klute <rainer.klute@epost.de> writes:
> Here's an Oracle example:

> create table BUSINESS_PROCESS
>         (ID NUMBER not null primary key,
>          BUSINESS_PROTOCOL varchar2(254),
>          PROTOCOL_VERSION varchar2(254),
>          DEFAULT_CONVERSATION_TIMEOUT NUMBER);

Oh, just another nonstandard datatype name then.  You could use CREATE
DOMAIN to create NUMBER as an alias for integer (or bigint or numeric,
depending on what range of values you're expecting).

varchar2 is harder since we don't have any provision for precision
options on domains.  You'll pretty much have to search-and-replace
that to varchar in your schema file :-(

			regards, tom lane

Mon, 06 Oct 2003 14:34:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Oracle/PostgreSQL incompatibilities 