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.
Migrating Schema objects 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.
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.
Migrating Oracle PL/SQL Constructs to PostgreSQL
USER DEFINED DATA TYPE
User-defined types or Object types store structured business data in its natural form and allow applications to retrieve it that way. For that reason they work efficiently with applications developed using object-oriented programming techniques.
COLLECTION
Oracle Collections allow you pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. PostgreSQL does not have similar feature like Collections, but has Arrays which can be leveraged to handle similar functionality like Oracle Collections.
BULK COLLECT
Normally a developer will use a cursor to retrieve and process multiple rows of data, one at a time, but there are performance problems when dealing with large numbers of rows using cursors. As we have seen, a cursor fetches one row at a time, holding a consistent view, until all rows have been retrieved or the cursor is closed.
A bulk collect is a method of fetching data where the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. The SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine. All the rows are retrieved with only 2 context switches. The larger the number of rows processed, the more performance is gained by using a bulk collect.
DBMS_OUTPUT.PUT_LINE
The Oracle dbms_output.put_line procedure allows you to write data to flat file or to display your PL/SQL output to a screen.
PostgreSQL database offers RAISE statement with different severity levels to either log the messages or display output in screen.
STANDARD.NEW_TIME
In Oracle Database, the NEW_TIME() function converts the date from a specified time zone to another specified time zone, and returns the result.