Home > Documentation > Oracle to PostgreSQL

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:

• DEFAULT
• FORCE / NO FORCE
• WITH CHECK OPTION
• WITH OBJECT IDENTIFIER
• WITH READ ONLY
• UNDER
• XMLType views are not supported by PostgreSQL

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.

• Oracle function CURTIME() is replaced by LOCALTIME(0) in PostgreSQL
• All occurrences of DAY($a) or 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)
• HOUR($a) is converted into EXTRACT(hour from $a)
• Simple form of using Oracle function INSTR($str1, $str2) can be replaced by POSITION($str2 in $str1).
  More comprehensive porting of this function can be found here
• Oracle function LCASE must be converted into PostgreSQL equivalent LOWER
• All occurrences of LOCATE($str1,$str2) must be replaced by POSITION($str1 in $str2)
• MINUTE($a) is converted into EXTRACT(minute from $a)
• MONTH($a) is converted into EXTRACT(month from $a)
• NVL($a, replace_with) can be converted into COALESCE($a, replace_with)
• Oracle function RAND must be replaced by PostgreSQL equivalent RANDOM
• Regexp support function REGEXP_LIKE($string, $pattern) is converted into PostgreSQL expression $string LIKE $pattern
• SECOND($a) is converted into EXTRACT(second from $a)
• Function SUBSTR($string, $from, $for) is converted into SUBSTRING($string, $from, $for)
• All occurrences of SYSDATE must be replaced by PostgreSQL equivalent CURRENT_DATE
• All occurrences of 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

• Oracle function UCASE is converted into UPPER that is synonym in PostgreSQL
• WEEK($a) is converted into EXTRACT(week from $a)
• Oracle function YEAR($a) must be replaced by EXTRACT(year from date($a))
• Oracle limitation by number of returned rows like where rownum <= 1000 is replaced by limit 1000 in PostgreSQL. Visit this page to learn more about of PostgreSQL equivalents of ROWNUM.

Click here to learn about migrating hierarchical queries from Oracle to PostgreSQL.