Thread: Oracle/PostgreSQL incompatibilities
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
> + 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.
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
>> + 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
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 >> >> >
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
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
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_VERSIONvarchar2(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