Oracle to PostgreSQL Complete Guide

Every database is different from one another in many ways. During migration from Oracle to PostgreSQL, you must know what Oracle database objects and features are supported in PostgreSQL and to what extent features need special handling in PostgreSQL.

This article covers the equivalent PostgreSQL database constructs for Oracle SQL and PL/SQL language elements and is intended to help you in doing code migration. The link provided for each section describes the differences and the proper way to handle in PostgreSQL.

Schema objects when migrating from Oracle to PostgreSQL

TABLE

Oracle has a huge list of parameters that you can provide during CREATE TABLE, whereas PostgreSQL has comparatively fewer options. Furthermore, all types of tables that Oracle database provides are not directly supported in PostgreSQL.

Following table types are not directly supported.

Index-organized table
Global temporary table (GTT)

CONSTRAINT

Oracle database provides following types of constraints.

PRIMARY KEY Constraint
FOREIGN KEY Constraint
UNIQUE KEY Constraint
CHECK Constraint
NOT NULL Constraint
REF Constraint

Constraints are supported in PostgreSQL but has syntax differences. PostgreSQL does not support Oracle’s REF constraint.

INDEX

Oracle database provides following types of Indexes.

B-tree indexes
Bitmap and bitmap join indexes
Function-based indexes
Domain indexes

Indexes are supported but has syntax differences. Domain Index is not supported in PostgreSQL.

VIEW

Views are persistent database objects that store a named SQL query. View are also referred as virtual tables because they do not store any data. You can create following types of views in Oracle database.
Simple view – A view having a single source table with no aggregate functions.
Complex view – A view with more than one source tables, aggregate functions, virtual columns or order by clause.

Views are supported in PostgreSQL, but following Oracle’s view features are not supported:

FORCE
WITH READ ONLY

MATERIALIZED VIEW

Materialized Views (also known as MViews) in Oracle have several refresh mechanism.

Fast
Complete
Force
On Demand
On Commit

Materialized view exists but refresh mechanisms are different.

PostgreSQL Materialized Views only support complete (full) refresh.
PostgreSQL Materialized Views may be refreshed either manually or using a job running the REFRESH MATERIALIZED VIEW command. Automatic refresh of Materialized Views require the creation of a trigger.
DML on Materialized Views is not supported.

SEQUENCE

There is difference between the syntax of sequence in Oracle and PostgreSQL.

--Oracle
Sequence_name.nextval
Sequence_name.currval

--PostgreSQL
nextval('sequence_name')
currval('sequence_name')

SYNONYM

PostgreSQL does not have Synonyms. But you can handle the similar functionality using SET search_path to access objects in other schemas.

TYPE

Convert to TYPE or DOMAIN in PostgreSQL. Unlike Oracle, you can not specify OR REPLACE with CREATE TYPE in PostgreSQL.

USER DEFINED FUNCTION

Functions available in PostgreSQL which are operationally more flexible compared to Oracle functions.

USER DEFINED PROCEDURE

Procedures are available in PostgreSQL 11 onwards and has lot of limitations in PostgreSQL. They are used only for specific purpose.

PACKAGE

Packages in Oracle provide a mechanism to encapsulate related procedures, functions, and other program units. PostgreSQL does not have package but has schemas which can be leveraged to group related objects. Accessing objects also remain same and no application changes required.

–Oracle
package_name.object_name

–PostgreSQL
schema_name.object_name

TRIGGER

Unlike Oracle, triggers in PostgreSQL don’t provide trigger code directly, but call a trigger function, which can be called from any number of triggers, though often they are customized for one particular event on one particular table.