Porting from Oracle to PostgreSQL

byPauloM erson February/2002

If youarestarting tousePostgreSQL oryouwillmigratefrom Oracledatabaseserver,I hopethisdocumenthelps.If youhaveJava

applicationsanduseJDBC,the“DatatypesandJDBC”sectionwillbeparticularlyuseful.

OracleandPostgreSQL bothconform tostandardSQL .However,theycontainseveralextensionsandimplementationdetailsthat

differentiateonefrom theother.Themostimportantdifferencesarelistedinthisdocument.

If youhavecommentsaboutthisdocument,pleaseemailme:pmerson@cs.cmu.edu.

1. SQ L Syntax,Functions,Sequences,Etc.

Oracle PostgreSQL

select sysdate from dual select ‘now’::datetime

Thereisno“dual”table

UnlikeotherRDBM S,PostgreSQL allowsa“select”withoutthe

”from”clause.Thisusedoesnotaffectportabilitybecause

thesyntaxtogetcurrenttimeisalreadyDBM S specific.

CREATE SEQUENCE seqname [ INCREMENT BY integer ] CREATE SEQUENCE seqname [ INCREMENT increment ]

[ MINVALUE integer ] [ MAXVALUE integer ] [ MINVALUE minvalue ] [ MAXVALUE maxvalue ]

[ START WITH integer ] [ CACHE integer ] [ CYCLE | NOCYCLE ] [ START start ] [ CACHE cache ] [ CYCLE ]

Oracle’s“createsequence”hasotherargumentsnotlistedhereandnot If youdon’tspecifyM AX VAL UE,thenthemaximum valueis

supportedbyPostgreSQL ,butthemaindifferenceistheneedof ‘by’and 2147483647forascending sequences.

“with”after“increment”and“start”.

If youdon’tspecify”M AX VAL UE”orif youusetheparameter

“NOM AX VAL UE”,thentheactuallimitis10 .

Oracle PostgreSQL

Toreturnthecurrentvalueandincrementthecounter: Toreturnthecurrentvalueandincrementthecounter:

sequence_name.nextval; nextval(‘sequence_name’);

Possibleusageinaselectstatement: Possibleusageinaselectstatement

select sequence_name.nextval from dual; select nextval(‘sequence_name’);

NotethatunlikeotherRDBM S,PostgreSQL allowsaselectwithoutthe

‘from’clause.Thisusedoesnotaffectportabilitybecausethesequence

syntaxisalreadyDBM S specific.

SELECT product_id, SELECT a,

DECODE (warehouse_id, 1, ’Southlake’, CASE WHEN a=1 THEN 'one'

2, ’San Francisco’, WHEN a=2 THEN 'two'

3, ’New Jersey’, ELSE 'other'

4, ’Seattle’, END

’Non-domestic’) FROM test

quantity_on_hand

FROM inventories

select employeeid, select employeeid,

NVL(hire_date, sysdate) coalesce(hire_date, 'now'::datetime)

from employee from employee

where employeeid = 10; where employeeid = 10;

Oraclealsohasa“coalesce”functionthatisageneralizationof the

commonlyusedNVL function.

Outerjoin–“(+)” Doesn’tsupportouterjoin.Theworkaroundistouseaunion.

Hierarchicalqueries–“CONNE CT BY” Nothing similar

SELECT product_id FROM inventories SELECT product_id FROM inventories

MINUS EXCEPT

SELECT product_id FROM order_items; SELECT product_id FROM order_items;

select unique col1, col2 from table1 select distinct col1, col2 from table1

InOracle“distinct”and“unique”keywordsaresynonymousintheselect PostgreSQL doesn’tallow “selectunique”.

statement.

Oracle PostgreSQL

Oraclerelationaloperatorsmayhaveaspacebetweenthecharacters.For PostgreSQL relationaloperatorsdoesn’tallow spaces,thecharactersthat

example,thefollowing selectwillwork: compoundanoperatormustbeconsecutivewhenthecommandis

parsed:

select id,name from employee where id > = 10;

// There are spaces and tabs between “>” and “=” select id,name from employee where id >= 10;

Togettheremainderof thedivisionof 10by4(modulo)usethe“mod” Togettheremainderof thedivisionof 10by4(modulo)

function: usethe“%”operator.(AndPostgreSQL hasmanyotherarithmetic

operators.)

select mod(10,4) from dual;

select 10 % 4;

The“ROW NUM ”pseudo-columnreturnsanumberindicating theorder Thereisn’tanythingequivalenttoOracleROW NUM.

inwhichOracleselectstherow.

However,youcanlimitthenumberof rowsreturnedbyaqueryusingthe

ROW NUM canbeusedtolimitthenumberof rowsreturnedbyaquery, “LIMIT”clause:

forexample:

select * from employees order by name limit 10;

select * from employees where rownum < 10 order by name;

Insomecases,it’spossiblethatthepseudo-columnOID maysubstitute

ROW NUM canbeusedintheprojectionasoneof thevaluesreturnedby ROW NUM ,althoughtheyhavedifferentbehavior.OID isaunique

thequery(firstlinehasvalue1,secondlinevalue2,andsoon): identifierof eachlinepertable,whileROW NUM isalways1,2,…,N

foreachdifferentquery.

select rownum, name from employees order by name;

select oid, name from employees order by name;

AndthequerythatusesROW NUM canhavejointables.If yourselectis

ajoinyou’llhaveadifferentOID foreachtable,becauseeachonehasan

OID column.

2.DatabaseServerGeneralCharacteristics

Oracle PostgreSQL

A view canbe“updatable”if someconditionsaresatisfied. Viewsarereadonly.

Oracle PostgreSQL

Transactionsareinitiatedimplicitly. BE GIN –initiatesatransactiondisabling thedefaultbehavior,

whichis“auto-commit”enabled,i.e.,acommitisperformedafter

Bydefault,the“auto-commit”behaviorisdisabled.

eachuserstatement.

InJava,weneedtowrite:con.setAutoCommit(false)

Tables,viewsandotherobjectscanbelogicallygroupedin Thereisnoschemasupport,butit’splannedforafutureversion.

“schemas”.A schemausuallymapstotheusernameof theuserthat

Thealternativeistouseseparatedatabases:Youhavetoconnectto

createdtheobjects(owner).Thus,atablecanbereferencedina

thespecific databaseandusethatconnectiontoexecuteyourSQL

statementas“schemaName.tableName”.Forexample:

command.

select * from mySchema.myTable;

However,if youhaveaSQL statementthatusestablesindifferent

(Oracle)schemas,youcannotuseseparate(PostgreSQL )databases;

thereisnodirectworkaround,you’llneedtorewritethecode.

Interactivecommandprompttool: Interactivecommandprompttool:

SQL *Plus psql

Oraclepermissionsaregranted/revokedto/from usersorroles.You PostgreSQL permissionsaregranted/revokedto/from usersor

cancreaterolesandgrant/revokerolesto/from users.Butyoucan groups.Youcancreategroupsandthenalterthegroups

“create”and“alter”groupstoinsertandremoveusers. inserting/removingusers.

Bydefault,apasswordisalwaysrequiredtoconnecttothedatabase. Bydefault,youcanconnecttothedatabasesimplybyspecifying the

databasename,nouserID andpasswordarerequired.

Youshouldfollow theinstructionsintheAdministrator’sGuideto

configurethepg_hba.conf fileinordertousepassword

authentication.

[pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict]

3.Data Typesand JDBC

Oracle JDBC PostgreSQL JDBC

NUMBER(p) where p is the precision, i.e., the getByte SMALLINT - 2 bytes getShort

number of digits getShort

INTEGER - 4 bytes getInt

getInt

getLong BIGINT - 8 bytes getLong

NUMBER(p,s) where p is the total number of getDouble NUMERIC(p,s) getBigDecimal

digits and s is the number of digits to the getBigDecimal

right of the decimal point REAL – 4 bytes, 6 decimal places getDouble

DOUBLE PRECISION – 8 bytes, 15 decimal places getDouble

Nothing similar SERIAL - 0 to +2147483647, typically used to getInt

create unique identifiers. Generates an

implicit sequence that is incremented when a

line is inserted in the table.

VARCHAR2(size) where maximum size is 4000 getString CHARACTER VARYING(n) where maximum n is 1 GB getString

VARCHAR(n) is an alias

CHAR(size) where maximum size is 2000 getString CHARACTER(n) where maximum n is 1 GB getString

CHAR(n) is an alias

It’s suggested that you use TEXT if n > 10 MB

LONG - Character data of variable length up to getString TEXT – variable length up to 1 GB getString

2 GB It’s suggested that you use TEXT if n > 10 MB

DATE – holds date and time getDate TIMESTAMP getTimestamp

getTime You still can use getDate to read a TIMESTAMP

getTimestamp column, but you will loose the time portion of

the data.

TIMESTAMP oracle.sql.

getTIMESTAMP

Nothing similar DATE – holds only the date (resolution is one getDate

day)

Nothing similar TIME – holds only the time (00:00:00.00 – getTime

23:59:59.99)

Oracle JDBC PostgreSQL JDBC

RAW(size) – binary data of length size bytes getBytes BYTEA getBytes

(max 2000)

LONG RAW – binary data or variable length up

to 2GB

Nothing similar BIT(n) – fixed length string of 1’s and 0’s (?)

BIT VARYING(n) – variable length string of 1’s

and 0’s

CLOB – character large object (max 4GB) getClob TEXT (max 1GB) getString

BLOB – binary large object (max 4GB) getBlob BYTEA (max 1GB) getBytes

BYTEA is not documented in PostgreSQL 7.1 but

it’s fully implemented; Jdbc 7.2-1.2 is

required though in order to use getBytes and

setBytes.

Besides TEXT and BYTEA, PostgreSQL supports

large objects as separate files. They are

stored in a separate table in a special

format, and are referred to from regular

tables by an OID value. More information:

http://www.postgresql.org/idocs/index.php?larg

eobjects.html

http://www.postgresql.org/idocs/index.php?jdbc

-lo.html

ROWID oracle.sql. Nothing similar

getROWID

Nothing similar BOOLEAN – can have the value TRUE, FALSE or getBoolean

Typically, char(1) is used to store a value NULL

that is translated to Boolean in the If you store '0' and '1' in a varchar(1) or

application logic. char(1) column, then the jdbc driver can

If you store '0' and '1' in a varchar2(1) or correctly interpret these values as boolean

char column, then the jdbc driver can false an true respectively using

correctly interpret these values as boolean ResultSet.getBoolean. However,

false an true respectively using PreparedStatement.setBoolean simply does not

ResultSet.getBoolean and work.

PreparedStatement.setBoolean If you use PostgreSQL BOOLEAN, then your Java

code can use getBoolean and setBoolean.

Oracle Spatial features ? Geometric data types: POINT, LINE, CIRCLE, org.

postgresql.

etc.

geometric. *

[pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict][pict]

Oracle JDBC PostgreSQL JDBC

Nothing similar Network address data types: INET, MACADDR, (?)

CIDR.

JDBC note:TypicallyPreparedStatement.setXxx isusedtosetthevalueof arguments(or“host”variables)insideSQL statements.

AndthereisacorrespondentResultSet.getXxx methodtoreadthevaluereturnedbyaqueryintojavavariables.E achget/setXXX

methodhasaspecific Javadatatypeorclassassociatedtoit(e.g.set/getInt dealswithint;set/getDate dealswith

java.sql.Date,etc.).Further,youcanusedifferentmethodstoreadthesamedatabasedatatype,butusuallythereisarecommended

method.Forexample,aBIGINT columncanbereadwithgetShort,getInt,getLong,getDouble,etc.,buttherecommendedmethod

isgetLong.

So,toindicatetheproperwaytouseeachdatatypeinJavaI simplylistedtherecommendedgetXxx JDBC method.

4.O therConsiderations:

• Thesetof operatorsandSQL functionsisverysimilarthoughOraclehasaricherset.Forexample,bothDBM S havetheconcatenation

operator“||”,aswellassubstr,upper,to_char andotherfunctionswiththesamesyntax.However,anyOraclefunctionthatisbeing

usedmusthaveitssyntaxcomparedtotheequivalentfunctioninPostgreSQL ,if suchexists.

• PostgreSQL lackstheabilitytoqueryacrossmultipledatabases.

• PostgreSQL ’sPL /pgSQL issimilartoOraclePL /SQL andcanbeusedtowritestoredfunctions.PostgreSQL doesn’thavepackagesor

procedures(onlyfunctions).M oreaboutthis:http://www.postgresql.org/idocs/index.php?plpgsql-porting.html

• BothDBM S havetriggersandthecreate trigger statementissimilar,butthecodeexecutedbythetriggerforPostgreSQL mustbe

inastoredfunctionwrittenbytheuser,whileinOracleyouhavetheoptionof writing thecodeinaPL /SQL blockinthecreate

trigger statement.PostgreSQL hasyetanadditionalresourcecalledthe“rulesystem”thatallowsthedefinitionof businesslogic that

isexecuteduponanevent.

• Thecreate table statementissimilarinbothDBM S.OnenoticeabledifferenceisthatPostgreSQL doesn’thavepctfree,

pctused,inittrans,andmaxtrans clauses.Theyalsodifferinthecreate database statement,mainlyintheargumentsand

clausesthatspecifystoragedetails.

[pict][pict][pict][pict][pict]

5.References:

• Oracle9idocumentation-http://download-east.oracle.com/otndoc/oracle9i/901_doc/nav/docindex.htm

• PostgreSQL documentation-http://www.postgresql.org/idocs/

• “OracletoPostgreConversion”-http://openacs.org/doc/openacs/html/oracle-to-pg-porting.html

• “PostgreSQL JDBC 2.0compliance”-http://lab.applinet.nl/postgresql-jdbc/

• Animportantsourceof informationisthePostgreSQL mailing lists:http://archives.postgresql.org/

Laman

Porting from Oracle to PostgreSQL

Posting Komentar

Rank

Multimedia Notes

Playlist

Catatan

Cari Blog Ini

Daftar Blog

Adv

Popular Post

Berlangganan melalui E-mail

Arsip Blog

Pengikut

Info Hangat

Artikel Blageziner