Each database is different from another in many ways. During migration from Oracle to PostgreSQL, you must first 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 will 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
| ORACLE | POSTGRESQL |
|---|---|
| Constraint (Primary Key, Foreign Key, Unique Key, CHECK, NOT NULL, REF) | Constraints are supported but has differences. PostgreSQL does not support Oracle’s REF constraint. See Details |
| Index | Indexes are supported but has differences. Domain index is not supported in PostgreSQL. See Details |
| VIEW | Views are supported in PostgreSQL, but some Oracle’s view features are not supported such as FORCE, WITH READ ONLY. See Details |
| Materialized View | Materialized View exists but refresh mechanisms are different. See Details |
| Sequence | There is difference between the syntax of sequence in Oracle and PostgreSQL. –Oracle Sequence_name.nextval –PostgreSQL nextval(‘sequence_name’) |
| Synonym | Synonym is not supported in PostgreSQL. Use SET search_path to access objects in other schemas. See Details |
| TYPE | Converted to TYPE or DOMAIN in PostgreSQL. See Details |
| Collections – Associative Array, Nested Table, Varray | Collections are not available in PostgreSQL. PostgreSQL provides arrays which are alternate solution for Oracle collections. See Details |
| Stored Function | Functions available in PostgreSQL which are operationally more flexible compared to Oracle functions. See Details |
| Stored Procedure | Procedures are available in PostgreSQL 11 onwards and has lot of limitations in PostgreSQL. They are used only for specific purpose. See Details |
| 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. See Details |
Oracle Commands when migrating to PostgreSQL
| ORACLE | PostgreSQL |
|---|---|
| FROM clause is mandatory in any SELECT statement. Oracle database provides “DUAL” table (A single row single column table) if working with literals or expressions. Example 1: SELECT 'ABC' res FROM DUAL;Example 2: SELECT sysdate res FROM DUAL; | FROM clause is not mandatory in PostgreSQL, DUAL can be removed. You can create a table or a view to ease code porting problems. Example 1: SELECT 'ABC' res;Example 2: SELECT current_date res; |
In Oracle, DELETE statement can work without FROM clause.DELETE <table_name> WHERE <column_name> = 'value'; | Add FROM clause in PostgreSQL DELETE statement manually.DELETE FROM <table_name> WHERE <column_name> = 'value'; |
Built-in functions and operators when migrating from Oracle to PostgreSQL
| ORACLE | PostgreSQL |
|---|---|
| NULL or Empty String (Both are same in Oracle database) IS NULL/ IS NOT NULL | NULL and Empty String both are treated differently in PostgreSQL. Oracle treats empty strings as NULL whereas this behavior differs in PostgreSQL and needs to be taken care. See Details |
| NVL | NVL function does not exist in PostgreSQL, use COALESCE function instead. Make sure to use NULLIF function within coalesce to handle empty strings.Example- COALESCE(nullif($1, ''), $2) where $1 and $2 are arguments to coalesce. |
| Pipe operator || (String concatenation operator) | PostgreSQL supports pipe “||” operator for string concatenation but the behavior differs from Oracle with respect to NULL. See Details |
| SYSDATE | Oracle’s SYSDATE function returns date and time. No SYSDATE function is available in PostgreSQL, but there are multiple methods to get the date and time and it is dependent on the application purpose.CURRENT_DATE – Provides only the date. Use if not concerned about time factor.NOW() / CURRENT_TIMESTAMP() – If time factor is required, but has to be same through out the transaction. Same for each statement within a transaction.CLOCK_TIMESTAMP() – When time factor is required and has to record the exact statement execution time within a transaction. Different for each statement within a transaction. |
| INSTR | PostgreSQL database does not have INSTR function, but has STRPOS function which works in a similar way, but has some differences.See Details |
| DECODE | PostgreSQL database does not have DECODE or a similar function, but you can convert DECODE to CASE expression.See Details |
| SUBSTR | SUBSTR function exists in PostgreSQL but has differences when the 2nd parameter is negative. In this case the SUBSTR function works without error but returns a different result. 1. Use Orafce SUBSTR function which returns the same result as Oracle in PostgreSQL. 2. Use AWS SCT extension function for SUBSTR. |
Oracle features when migrating to PostgreSQL
| ORACLE | PostgreSQL |
|---|---|
| MERGE | PostgreSQL doesn’t have a direct MERGE-like construct. However, in PostgreSQL 9.5, the ON CONFLICT clause was added to INSERT, which is the recommended option for many of the Oracle MERGE statements conversion in PostgreSQL.See Details |
| JOIN | Except Oracle native outer joins, most of the Join types works same in PostgreSQL as well and might not require code change during conversion. See Details |
SUBQUERYSELECT * FROM (SELECT * FROM table_a); | PostgreSQL requires inline SELECT (subquery in FROM clause) surrounded by parentheses must be provided an alias. The alias is not mandatory for Oracle.SELECT * FROM (SELECT * FROM table_a) AS foo;See Details |
| Hierarchical queries like CONNECT BY, START WITH, LEVEL etc. | PostgreSQL does not have hierarchical CONNECT BY queries. PostgreSQL provides WITH RECURSIVE clause which is an alternate option for hierarchical queries. All of Oracle CONNECT BY, START WITH queries can be converted to PostgreSQL equivalent using WITH RECURSIVE clause. See Details |
ROWNUMSELECT ROWNUM rn, empno, ename, sal | Oracle’s pseudocolumn ROWNUM returns a number which is assigned at the moment when data is read, but before the ORDER BY is executed. There is no direct ROWNUM like feature available in PostgreSQL. You might consider to replace ROWNUM by using ROW_NUMBER() OVER () which will work in many cases. SELECT row_number() over() rn, empno, ename, salNote: The order of rows may not be same in Oracle and PostgreSQL. If you wish to have same rows, you need to specify explicit ORDER BY clause in a subquery. |
| ROWID | Oracle’s pseudocolumn ROWID returns the physical address of a table row, encoded in base64. In PL/SQL programs they are used to temporarily cache the address of rows to locate them easier a second time. Postgres has ctid which serves the same purpose. |
| Anonymous Block | Syntax change. See Details |
| SYS_REFCURSOR | REFCURSOR |
| CURSOR | Cursor feature is available in PostgreSQL, but need to be handled correctly while migrating to PostgreSQL. See Details |
| NEW_TIME – PL/SQL NEW_TIME function converts a date and time from one timezone to a date in another timezone. | PostgreSQL does not have any similar function like NEW_TIME. Create wrapper function. See Details |
| DBMS_OUTPUT.PUT_LINE | RAISE statement in PostgreSQL is used to display messages, debugging and raise errors.See Details |
| BULK COLLECT | No such equivalent syntax in PostgreSQL. But you can leverage array_agg function available in PostgreSQL. See Details |
| NO_DATA_FOUND and TOO_MANY_ROWS | By default SELECT statements in PLpgSQL do not throw these exceptions. You need to add keyword STRICT after any keyword INTO in all selects, when you need to keep single row checking in stored PLpgSQL code. |