Convert Oracle views into PostgreSQL format
|[Oracle to PostgreSQL Converter] [About Migration] [Types Mapping]|
Database migration from Oracle to PostgreSQL involves converting Oracle views into the destination format. Since syntax of queries in Oracle and PostgreSQL is not identical and also these two DBMS have distinguished sets of built-in functions, it is necessary to convert each SQL statement before passing it to the destination DBMS.
Oracle provides multiple features for creating views that are not supported by PostgreSQL. Such features must be removed during conversion:
Also, Oracle supports custom syntax of LEFT JOIN that is not accepted by PostgreSQL:
SELECT t1.f2, t2.f2 FROM t1, t2 WHERE t1.f1=t2.f1 (+)
Such queries must be converted according to ANSI SQL standard on LEFT JOIN:
SELECT t1.f2, t2.f2 FROM t1 LEFT JOIN t2 ON t1.f1=t2.f1
Finally, all embedded Oracle functions that are missing in PostgreSQL must be replaced by the appropriate equivalents.
CURTIME()is replaced by
DAYOFMONTH($a)must be converted into the following expression:
EXTRACT(day from date($a))::integer
DateAdd($Date, $Format, $Days, $Months, $Years)can be converted into this expression:
$Date + cast('$Days day' as interval) + cast('$Months months' as interval) + cast('$Years years' as interval)
INSTR($str1, $str2)can be replaced
by POSITION($str2 in $str1).
SYS_GUID()can be replaced by
uuid_generate_v1(). In early versions of PostgreSQL (before v9.4) this function depended on the OSSP UUID library. In order to get version-independent solution, the following expression may be used:
SELECT md5(random()::text || clock_timestamp()::text)::uuid
Click here for more articles about Oracle, PostgreSQL and other popular database management systems.