Re: [HACKERS] Oracle/PostgreSQL incompatibilities
On Fri, 03 Oct 2003 11:53:05 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Rainer Klute <[EMAIL PROTECTED]> 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 999 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: [EMAIL PROTECTED] Körner Grund 24 Telefon: (0231) 511693 D-44143 Dortmund Mobil: (0172) 2324824 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Oracle/PostgreSQL incompatibilities
Rainer Klute <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] 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 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Oracle/PostgreSQL incompatibilities
Rainer Klute <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 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.
signature.asc
Description: This is a digitally signed message part
