Skip site navigation (1) Skip section navigation (2)

Oracle/PostgreSQL incompatibilities

From: Rainer Klute <rainer(dot)klute(at)epost(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Cc: klute(at)rainer-klute(dot)de
Subject: Oracle/PostgreSQL incompatibilities
Date: 2003-10-02 06:37:12
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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 be the
     same as the parent table. This would increase Oracle

   + 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

                           Rainer Klute IT-Consulting GmbH
  Rainer Klute             E-Mail:  klute(at)rainer-klute(dot)de
  K├Ârner Grund 24          Telefon: +49 172 2324824
D-44143 Dortmund           Telefax: +49 231 5349423


pgsql-hackers by date

Next:From: Peter EisentrautDate: 2003-10-02 06:40:18
Subject: Re: 7.4 status
Previous:From: Peter EisentrautDate: 2003-10-02 06:37:08
Subject: Re: 7.4 status

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group