How to migrate from Oracle to PostgreSQL

PostgreSQL is one the most advanced open source database management systems having strong community of enthusiasts that are continuously improving existing capabilities and add new features.On the other hand, Oracle licensesare very expensive and there is additional cost for extra features. This is the reason why many organizations migrate from Oracle to PostgreSQL despite of the fact the migration takes a lot of time and efforts.

Before beginning the procedure of database migration, it is important to understand which objects, properties and code must be migrated. There is no sense in migrating database objects that are no longer required. For example, decide if any historical data, backup data and temporary table from past maintenanceare required or not.

There areseveraldifferences between Oracle to PostgreSQL that must be subject of special attention during the migration:

• Dual Table. Oracle uses virtual table ‘DUAL’ for SELECT statements where the table name is not necessary. Since PostgreSQL does not require FROM clause, table DUAL can be omitted.
• Hierarchical queries.Oracle uses ‘START WITH . . . CONNECT BY’ syntax for hierarchical queries, PostgreSQL uses ‘WITH RECURSIVE’ for the same purpose.
• Empty Strings vs NULL.Unlike Oracle, PostgreSQL distinguishes empty strings and NULL values. Oracle allows to check if string is empty using the IS NULL operator, but PostgreSQL returns FALSE for an empty string and TRUE for a NULL one.
• Oracle and PostgreSQL implement sequences using different syntax and so these objects need to be updated as follows: sequence.nextval becomes nextval(‘sequence’).
• Instead of packagesPostgreSQL uses schema architecture to group tables,functions and procedures.
• Unlike Oracle,PostgreSQL does not support synonyms. In place ‘CREATE SYNONYM’ PostgreSQL allows to use ‘SET <search_path>’ to include the remote definition.
• Joins with (+). Oracle supports special operator (+)for left and right outer joins. PostgreSQL does not have this feature;it requirestraditional syntax of JOIN commands.

The challenges listed above proves that Oracle to PostgreSQL migration is a hard, tedious procedureand the human factor may cause data loss or corruption when running it manually. Database professionals often use special tools to automate database migration and avoid the related risks.

There are few software companies that develop database migration tools for Oracle and PostgreSQL. One of such solutions is Oracle to PostgreSQL by Intelligent Converters, vendor specializing in database migration betweenall popular DBMS such as Oracle, PostgreSQL, MySQL, SQL Server, SQLite, MS Access and IBM DB2 since 2001.

Product Capabilities

• All modern versions of Oracle and PostgreSQL are supported including SAAS variations
• The following database objects are converted: schemas, data, indexes, constraints, views
• The converter uses low-level export and import techniques to reach high performance of the database migration
• Command line is supported for purpose of automation
• The converter can merge and synchronize existing PostgreSQL database with Oracle data
• Conversion settings are stored into profile for next use

Besides basic features listed above Oracle to PostgreSQL converter offers filtering data via SELECT-queries that is powerful capability to pre-process the data before migrating to the target database. Here are few examples of using SELECT-queries for different purposes.

• Extracting subset of data

SELECT * FROM People WHERE BirthDate BETWEEN to_date(’1-JAN-80’) AND to_date(’31-DEC-89’)

• Selecting and renamingcertain columns

SELECT fn AS First_Name, ln AS Last_Name, bd as Birth_Date FROM People

• Skipping NULL values

SELECT * FROM Docs WHERE Comment IS NOT NULL

• Merging two tablesinto single one

SELECT T1.code, T1.name, T1.description, T2.image

FROM Products T1

INNER JOIN ProductLines T2 ON T1.productline = T2.productline;