Thread: Mapping Oracle types to PostgreSQL types
Dear friends, I would like to port Compiere CRM from Oracle to PostgreSQL (and release it for free). At first I would like to convert the data schema. This is not difficult as Compiere is written using portable types like NUMBER (i,d) which can be replaced by NUMERIC (i,d), etc... A series of Search/Replace is sufficiant. There are other solutions in Contrib to connect to Oracle and export the data (Bruce). Don't blame me to search in another (silly) direction... The point here is that I would like to use the CREATE TYPE or CREATE DOMAIN syntax to map Oracle types to PostgreSQL types. Therefore I can say "Guys, Oracle is now mostly compatible with PostreSQL". In PostgreSQL, I used CREATE TYPE syntax to map Oracle nvarchar2 -> PostgreSQL varchar (see code #1). The code seems to be the equivalent of "CREATE DOMAIN nvarchar2 as varchar;" Now I can create tables with nvarchar2 but not nvarchar2(lenght). Is there a way to map Oracle nvarchar2(lenght) to PostgreSQL varchar(lenght) in PostgreSQL 7.3? Are there plans to allow such mapping in the future using the CREATE DOMAIN syntax? Best regards, Jean-Michel Pouré ********************************************************************** Code #1 --DROP TYPE nvarchar2 CASCADE; CREATE OR REPLACE FUNCTION oracle_nvarchar2in(cstring, oid, int4) RETURNS nvarchar2 AS 'varcharin' LANGUAGE 'internal' IMMUTABLE STRICT; COMMENT ON FUNCTION oracle_nvarchar2in(cstring, oid, int4) IS '(internal)'; CREATE OR REPLACE FUNCTION oracle_nvarchar2out(nvarchar2) RETURNS cstring AS 'varcharout' LANGUAGE 'internal' IMMUTABLE STRICT; CREATE TYPE nvarchar2 (INPUT=oracle_nvarchar2in, OUTPUT=oracle_nvarchar2out, DEFAULT='', INTERNALLENGTH=-1, ALIGNMENT=int4, STORAGE=EXTENDED); COMMENT ON TYPE nvarchar2 IS 'Oracle type nvarchar2(length) mapped to PostgreSQL type varchar(lenght)';
Jean-Michel POURE writes: > Is there a way to map Oracle nvarchar2(lenght) to PostgreSQL varchar(lenght) > in PostgreSQL 7.3? Are there plans to allow such mapping in the future using > the CREATE DOMAIN syntax? No to both. Doing this would most likely require making the affected type names be reserved words in the grammar or sacrifice some other functionality, which seems a high price to pay for this cosmetic feature. -- Peter Eisentraut peter_e@gmx.net
Jean-Michel POURE wrote: > Dear friends, > > I would like to port Compiere CRM from Oracle to PostgreSQL (and release it > for free). > > At first I would like to convert the data schema. This is not difficult as > Compiere is written using portable types like NUMBER (i,d) which can be > replaced by NUMERIC (i,d), etc... A series of Search/Replace is sufficiant. > There are other solutions in Contrib to connect to Oracle and export the data > (Bruce). Don't blame me to search in another (silly) direction... Rather than declaring numeric, create them as integer/float of appropriate size and add appropriate constraints. Numeric can be slower for large data load w.r.t native integers. > > The point here is that I would like to use the CREATE TYPE or CREATE DOMAIN > syntax to map Oracle types to PostgreSQL types. Therefore I can say "Guys, > Oracle is now mostly compatible with PostreSQL". You can create some sql scripts which can natively migrate from oracle to postgresql. Contrib could host them or gborg. So what postgresql would say is, create a database and run the scripts and many of the oracle migration gotchas will be automatically taken care of. Including such features in core postgresql is rather hard sell to postgresql developers. Especially when there is a rather simple workaround. HTH Shridhar
Shridhar Daithankar wrote: > Jean-Michel POURE wrote: > >> Dear friends, >> >> I would like to port Compiere CRM from Oracle to PostgreSQL (and >> release it for free). >> >> At first I would like to convert the data schema. This is not >> difficult as Compiere is written using portable types like NUMBER >> (i,d) which can be replaced by NUMERIC (i,d), etc... A series of >> Search/Replace is sufficiant. There are other solutions in Contrib to >> connect to Oracle and export the data (Bruce). Don't blame me to >> search in another (silly) direction... > > > Rather than declaring numeric, create them as integer/float of > appropriate size and add appropriate constraints. Numeric can be > slower for large data load w.r.t native integers. float is *not* an alternative to numeric. While integer or int8 should be usable for NUMBER(i), the non-rounding precision of numeric is vital for such an application. Regards, Andreas
Peter Eisentraut <peter_e@gmx.net> writes:
> Jean-Michel POURE writes:
>> Is there a way to map Oracle nvarchar2(lenght) to PostgreSQL varchar(lenght)
>> in PostgreSQL 7.3? Are there plans to allow such mapping in the future using
>> the CREATE DOMAIN syntax?
> No to both. Doing this would most likely require making the affected type
> names be reserved words in the grammar
Right. At the moment, *all* the type names that support parenthesized
options are hard-wired into the grammar. I think this is probably
unavoidable because otherwise there is a conflict between interpreting
"foo(3)" as a type name and interpreting it as a function call. (But
if anyone can think of a way around that, I'm all ears.)
Since varchar(n) is SQL-standard syntax, can't you simply adopt the more
standard name for both databases?
regards, tom lane
On Fri, 2003-10-17 at 04:10, Jean-Michel POURE wrote: > Dear friends, > > I would like to port Compiere CRM from Oracle to PostgreSQL (and release it > for free). This would be wonderful. However, I believe the guys at Compiere tried to do this already and gave up on porting it to postgresql due too a couple of PostgreSQL limitations. I don't remember what they are exactly, I think it had to do with nested transactions, maybe savepoints, not sure exactly. You should be able to find some mention of this on their site. It sounded to me like they use a lot of Oracle features. Good Luck!
On Fri, 17 Oct 2003, Tom Lane wrote: > Since varchar(n) is SQL-standard syntax, can't you simply adopt the more > standard name for both databases? > A long time ago Oracle made the varchar type equivalent to char and once people complained about the excess space used by short entries they came out with varchar2 which they've maintained every since valuing backwards compatability more than the sql standard. Kris Jurka
Le Vendredi 17 Octobre 2003 16:32, Matthew T. O'Connor a écrit : > This would be wonderful. However, I believe the guys at Compiere tried > to do this already and gave up on porting it to postgresql due too a > couple of PostgreSQL limitations. I don't remember what they are > exactly, I think it had to do with nested transactions, maybe > savepoints, not sure exactly. You should be able to find some mention > of this on their site. It sounded to me like they use a lot of Oracle > features. There are only a few limitations in PostgreSQL like nested transaction, updatable cursors and Oracle PL error handling. Can we call these "limitations"? Most of us can live without them. These "limitations" are only a small portion of the code (sometimes a few lines like updatable cursors). Cheers, Jean-Michel
Le Friday Oct 17, 2003 at 10:10:26AM +0200, Jean-Michel POURE a écrit : > Dear friends, > > I would like to port Compiere CRM from Oracle to PostgreSQL (and release it > for free). Hi Jean-Michel, I did Red-Hat CCM Migration from Oracle to PostgreSQL a year ago when I was the dba of IdealX, a french Open Source Services company in France, you may know it. Since the job has been done for french public affairs, I hope IdealX and this ministry did publish it GPL, I'm gonna get some informations, I'll keep you on touch. I do copy an Oracle production database everyday into a PostgreSQL one, for web sites needs: production database is under Oracle, web reporting database is on a LAPP server. (not LAMP ;-).. ). I succeeded using oracle contrib (look at contrib/oracle or directly to Gilles's DAROLD web at: http://www.samse.fr/GPL/). Ora2pg is fast, simple and accurate, it handles PG schemas too, etc.. Beware of numeric(x,y) PG type, it's really slow, prefer integer datatype. For example, translate a NUMERIC(3,2) into INTEGER. Just *100 the value, and modify client side (/100), it will be really faster (that was the simple solution we found on another migration project at IdealX...). [ As I told it here (http://www.geocrawler.com/archives/3/104/2002/5/0/8597590/)a year ago: « Be aware that NUMERIC(x,y) in PG is very powerfull for atypic uses, forexample (18,9) ... and not that much for other uses.That's my point ofview.» I must admit I didnt test it from one year. So with the speed you PGhackers work, offering each release a new *major* release(HISTORY itemsgrowth can show this: amazing..), I think maybe this is no more true... ] I'd like to help you in your project, since I'm Oracle/PG experimented DBA, and I need a good CRM solution for personal and profesional purposes. Please tell me if my experience can help you in any way, I'd be really glad in participating your project. I can offer public CVS, web, etc.. for the project if you want. Overall, I think we're both French ;-)) Cheers, -- Jean-Paul ARGUDO DBA Oracle, PostgreSQL, MySQL, Ingres Membre de l'April http://www.april.org
Dear Jean-Paul, > Please tell me if my experience can help you in any way, I'd be really > glad in participating your project. Thanks for your proposal, welcome in the team. In short, we plan to port Compiere to PostgreSQL and submit the changes back to Compiere team. There is no evidence so far that Compiere will accept the changes as their main developer sells Oracle licenses. At present, the team is: - Vincent Harcq <vha@audaxis.com> for the Java part or Compiere. - Jean-Paul ARGUDO <jpargudo@free.fr> and Jean-Michel Pouré <jm@poure.com> for the database migration. > I can offer public CVS, web, etc.. for the project if you want. Thanks. During the week-end, I will set up a first web page and will contact you back on Monday. In a first time, our goal could be to describe the port in details, so to convince Compiere community to migrate to PostgreSQL. Without support from the Compiere community, there can be no port... Best regards, Jean-Michel