Since both Oracle and Postgres are both advanced sophisticated database management systems, there are some challenges in migration between these two systems. This article explores the most valuable and common Oracle to Postgres migration challenges.
DUAL Table
Oracle has service table DUAL created along with any database in the schema of the user SYS. It is acceptable for any user and its primary purpose is to help computing constant expressions within the SELECT statements. PostgreSQL does not have such table, so the corresponding FROM clause must be simply omitted in most cases.
Empty String vs NULL
Oracle treats empty strings from NULL values pretty similar in the literal context. For example, result of concatenating NULL and string is a string in Oracle, while the samewill give NULL in Postgres.
Oracle comparison operation $value IS NULL can be used to check empty strings,while the same expression is TRUE for NULL values only and is FALSE for empty string in Postgres.
Rownum
Rownum is another of Oracle to Postgres migration challenges. This is Oracle pseudo-column that contains numbers indicating order of selected rows from table or joined set of tables: first row has rownum=1, second row’s rownum=2, etc. Postgres does not have direct equivalent of rownum, so it should be migrated from Oracle in different ways depending on scope of use.
If Oracle uses rownum to limit number of resulting rows, it can be migrated into Postgres limit statement. For example, in Oracle:
SELECT * FROM mytable WHERE ROWNUM <= 10;
The same in Postgres will look like:
SELECT * FROM mytable LIMIT 10;
If rownum is used to enumerate rows in the output rowset, it can be implemented in Postgres as follows:
SELECT row_number() OVER () ROWNUM, * FROM mytable
Synonyms
Oracle provides synonyms to simplify accessing remote objects. Postgres does not have the same feature, so it cannot be migrated directly. Instead, Postgres allows to set up remote definition through ‘SET search_path’. For example, Oraclestatement:
CREATE SYNONYM loc.tbl FOR rem.tbl;
should be migrated in PostgreSQL as follows:
SET search_path TO ‘loc.tbl’;
Sequences
Although syntax of creating sequences in Oracle and PostgreSQL is quite similar, there is minor difference in getting next value. In Oracle:
Seq_name.nextval
The same in PostgreSQL is:
Nextval(‘Seq_name’)
Built-in Functions
Built-in functions are used in stored procedure, functions, triggers and views. So, correct migration of Oracle functions missing in PostgreSQL is a very important part of the overall database migration. The table below illustrates appropriate conversion of the most popular built-in functions from Oracle to PostgreSQL:
| Oracle | PostgreSQL |
| ADD_MONTHS($date,$n_month) | $date + $n_months * interval ‘1 month’ |
| DECODE($exp, $when, $then, …) | CASE $exp WHEN $when THEN $then … END |
| INSTR | STRPOS |
| MONTHS_BETWEEN($date2,$date1) | 12*extract(year from age($date2::date,$date1::date)) + extract(month from age($date2::date,$date1::date)) |
| SYSDATE | CURRENT_DATE |
| SYS_GUID | uuid_generate_v1 |
If you find all Oracle to Postgres migration challenges listed above too complicated to handle manually, it is reasonable to use special software that can automate most of the underlying conversions. Oracle to PostgreSQL Code Converter is one of software tools to automate migration of stored procedures, functions, triggers and views between the two DBMS.
Visit the official site of the product to learn what kind of Oracle to Postgres migration challenges it is able to solve.
