Oracle/PostgreSQL incompatibilities - Mailing list pgsql-hackers

From Rainer Klute
Subject Oracle/PostgreSQL incompatibilities
Date
Msg-id 20031002083712.011a142b.klute@rainer-klute.de
Whole thread Raw
Responses Re: Oracle/PostgreSQL incompatibilities
Re: Oracle/PostgreSQL incompatibilities
List pgsql-hackers
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
bethe    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.deKörner Grund 24          Telefon: +49 172 2324824 
D-44143 Dortmund           Telefax: +49 231 5349423


pgsql-hackers by date:

Previous
From: Patrick Welche
Date:
Subject: Re: ecpg doesn't compile (datetime.h/dtime_t)
Next
From: Tom Lane
Date:
Subject: Re: [SQL] HeapTuple->t_tableOid==0 after SPI_exec