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.