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.

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

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

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

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