Oracle and PostgreSQL are well-known database management systems with a broad variety of capabilities and functions. When these two DBMSs are compared, it becomes clear that Oracle has more restrictive licensing conditions and a higher total cost of ownership, while PostgreSQL is open source and distributed under a permissive license equivalent to BSD or MIT. This is why many businesses are considering migrating their databases from Oracle to PostgreSQL.

The conversion of an Oracle database to PostgreSQL may be thought of as a series of the following steps:

• Export the definitions of Oracle tables in the form of data definition language (DDL) statements.
• Convert DDL statements to PostgreSQL format, including correct type mapping and substitution of Oracle embedded functions that PostgreSQL does not support.
• Import the resulting table definitions into the PostgreSQL database that will be used.
• Export Oracle data in the comma separated values (CSV) format from the source database to an intermediate storage.
• Convert the data to PostgreSQL format, converting binary data, dates, and escaping special characters in text fields as appropriate.
• Import the CSV files generated into the target database.
• Generate SQL statements and source code for exporting Oracle views, stored procedures, and triggers.
• Convert these statements and source code to the desired format.
• Import it into PostgreSQL.

The processes outlined above demonstrate how difficult it is to manually convert an Oracle database to PostgreSQL format. This time-consuming technique requires considerable effort, and the human aspect may result in data loss or corruption. Special software may be used to automate database migration and eliminate associated hazards.

There are a few software businesses that provide solutions for automating Oracle to PostgreSQL database transfer. Among these options is the Oracle to PostgreSQL conversion tool built by Intelligent Converters, a software business that has been active in the database migration space since 2001.

Product Features

1. Conversion of the following database objects: definitions of tables, their contents, constraints, indexes, and foreign keys
2. The converter makes use of direct connection and bulk insert methods to optimize the database conversion process.
3. It supports all versions of Oracle and PostgreSQL, as well as their “software as a service” (SAAS) variants.
4. For automation purposes, the Oracle to PostgreSQL converter has command line interfaces.
5. The converter enables the synchronization and merging of existing PostgreSQL databases with Oracle data.
6. Conversion preferences are saved in the profile.

Apart from these fundamental aspects, the Oracle to PostgreSQL converter supports such versatile and powerful functionality as data filtering using SELECT-queries. This feature enables you to pick columns and records for conversion, as well as perform various data modifications before to converting them to PostgreSQL format. Consider the following examples of how to use SELECT-queries for various reasons.

1) Record filtering:

SELECT * FROM Orders WHERE StartDate IS BETWEEN to date(’15-JAN-16′) AND to date(’15-FEB-16′)

2) Selected and renamed certain columns:

SELECT fname AS FirstName, lname AS LastName, addr as Address FROM People

3) Ignore NULL values: SEARCH FOR * IN MYTABLE WHERE comments IS NOT NULL WHERE comments IS NOT NULL

4) Consolidate two tables:

SELECT T1. code, T1.name, T1. description, T2. image FROM T1. code, T1.name, T1. description, T1. descriptionFROM PRODUCTST1 INNER JOIN PRODUCT_LINES T2 ON T1 = T2;

As with other Intelligent Converters products, the Oracle to PostgreSQL database transfer solution includes unlimited support and a one-year subscription to the company’s upgrades.