Porting between Oracle and PostgreSQL

Advanced How-tos Data Intermediate

Arun Gavhane Senior Systems Engineer

This article discusses the possible complications that can arise when migrating databases from Oracle to PostgreSQL. The steps for migration are covered and issues and their fixes are identified:

  1. Approach to migration
  2. Schema migration
  3. Code migration
  4. Data migration

 

When migrating database objects from Oracle to PostgreSQL you may face issues as there are some incompatibilities between Oracle and PostgreSQL. Before performing the migration you need to find out the incompatibilities between these heterogeneous databases. Because of the challenges you may face, you may need to make some manual changes in the syntax of the database objects before you perform a migration between Oracle to PostgreSQL.

For the migration you may use the open source tool Ora2pg, an EDB Migration Toolkit (MTK), or EDB Migration Portal.

 

Approach to migration

You need to set the approach of migration. Following the steps below can be helpful:

1. Schema migration

2. Code migration

3. Data migration

 

Schema migration

You need to fetch the Oracle schema (that is, DDLs) first and then insert that schema into PostgreSQL, after making the appropriate changes. You may face several challenges here that require you to make some manual changes in the DDLs before you perform the migration.

Below are some changes to consider:

A) Parameters 

Parameters need to be considered for the creation of a table. Oracle has a huge list of parameters that you can provide to create a table, whereas PostgreSQL has comparatively fewer options. Here is a general example showing the differences when creating the same table in Oracle and PostgreSQL:

Oracle Example

SQL> CREATE TABLE "U1"."SC_TENORS" 

("TENOR_NAME" VARCHAR2(20) NOT NULL ENABLE,

"TENOR_VALUE" NUMBER(*,0) NOT NULL ENABLE,

                    "TENOR_DESC" VARCHAR2(30) NOT NULL ENABLE,

                    CONSTRAINT "SC_TENORS_PK" PRIMARY KEY ("TENOR_NAME") ENABLE

                     ) 

                    ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING

                    TABLESPACE "TBS1"

                    PCTTHRESHOLD 50 INCLUDING "TENOR_VALUE" OVERFLOW

                    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

                    TABLESPACE "TBS1"

;

 

NOTE: The table name SC_TENORS  in Oracle is case-insensitive

 

PostgreSQL Example

postgres=# CREATE TABLE sch1."SC_TENORS" (

                    "TENOR_NAME" varchar(20) NOT NULL,

                    "TENOR_VALUE" numeric(38) NOT NULL,

                    "TENOR_DESC" varchar(30) NOT NULL,

                    CONSTRAINT "SC_TENORS_PK1" PRIMARY KEY ("TENOR_NAME")

                   ) tablespace tbspc1 ;

CREATE TABLE



postgres=# \dt

           List of relations

 Schema |   Name    | Type  |  Owner   

--------+-----------+-------+----------

 sch1   | SC_TENORS | table | postgres

(1 rows)





postgres=# CREATE TABLE sch1.SC_TENORS (

                    TENOR_NAME varchar(20) NOT NULL,

                    TENOR_VALUE numeric(38) NOT NULL,

                    TENOR_DESC varchar(30) NOT NULL,

                    CONSTRAINT "SC_TENORS_PK2" PRIMARY KEY (TENOR_NAME)

                   ) tablespace pg_default ;

CREATE TABLE

postgres=# \dt

           List of relations

 Schema |   Name    | Type  |  Owner   

--------+-----------+-------+----------

 sch1   | SC_TENORS | table | postgres

 sch1   | sc_tenors | table | postgres

(2 rows)

 

B) Datatypes

The following dataypes require special attention when migrating from Oracle to PostgreSQL:

1. VARCHAR2 is not supported in PostgreSQL and will generate errors.
Workaround: VARCHAR2 can be replaced with VARCHAR.

2. NUMBER is not supported in PostgreSQL.

Workaround: NUMBER can be replaced with INT, SMALLINT, BIGINT, NUMERIC(n).

3. DATE is available in Oracle as well as PostgreSQL. The DATE datatype in Oracle shows both date and time, but the PostgreSQL DATE data type returns only date with no time.

Workaround: If your requirement is date with time, then use the datatype TIMESTAMP.

 

C) ROWID

Oracle's pseudocolumn ROWID returns the physical address of a table row, PostgreSQL instead has ctid, which serves the same purpose.

 

D) Virtual Columns 

Virtual Columns do not exist in PostgreSQL, You can use view instead.

 

E) Global indexes  

Oracle has global indexes over partitions, they are not supported in PostgreSQL.

 

F) Global temporary tables 

Unlike Oracle, global temporary tables do not exist in PostgreSQL.

 

G) Object name field limit 

Oracle has higher object name field limit than PostgreSQL.

 

H) UNIQUE

The UNIQUE keyword is currently not supported in PostgreSQL. Users can substitute the functionally equivalent “DISTINCT” keyword.

Usage (ORACLE):

SELECT UNIQUE deptid FROM dept;

 

Workaround (PostgreSQL):

SELECT DISTINCT deptid FROM dept;

 

DUAL Table

DUAL table is a dummy table used by Oracle, as for every select statement a FROM clause is mandatory. In PostgreSQL this clause can be omitted and the direct function can be used.

Workaround: When migrating, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare—and peculiar.

Oracle example 

SQL> select current_timestamp from dual;



CURRENT_TIMESTAMP

----------------------------------------------------------------------

17-MAR-20 04.32.32.465956 AM -07:00

 

PostgreSQL example

postgres=# select current_timestamp ;

       current_timestamp       

-------------------------------

 2020-03-17 04:32:49.218165-07

(1 row)

 

Code migration

This is the most important and time-consuming task in the migration. Code gets developed based on the business logic and due to a lot of differences in PL/SQL and PL/pgSQL, a major part of the migration requires manual intervention.

These are some of the most common differences:

A)  There is no substitution of Oracle’s packages in PostgreSQL. You must create a schema for it instead and use it.

B) To print something, Oracle uses “dbms_output.put_line('Hello World');”,  while PostgreSQL uses “raise notice 'Hello World!';”.

An Oracle-style (PL/SQL) procedure for printing “hello world” would look like this:

SQL> create or replace procedure pro1

     as

     begin

     dbms_output.put_line('Hello World');

     end;

     /

Procedure created.

SQL> exec pro1; 

Hello World

PL/SQL procedure successfully completed.

 

 We can convert this into a PostgreSQL-style procedure (PL/pgSQL) like this:

postgres=# create or replace procedure pro1()

                         language plpgsql

                         as $$

                         begin

                         raise notice 'Hello world';

                         end; $$;

                         CREATE PROCEDURE



postgres=# call pro1();

                  NOTICE:  Hello world

                  CALL

 

C) Exiting from IF statements looks like this:

In Oracle:                

  EXIT

          WHEN cur_get_objects%NOTFOUND; 

 

In PostgreSQL:

 IF NOT FOUND THEN EXIT; END IF;

 

Along with the above, here are a few additional differences:

—Oracle converts names of schema, tables, columns, and functions to UPPER CASE, unless quoted, and PostgreSQL converts them to lower case, unless quoted. It is safe whether the application quotes the identifiers or not.

—Oracle gives every user their own schema, by default. For PostgreSQL, create a user and schema by the same name. By default the first component in search_path is $user.

You are now connected to database "postgres" as user "arun".

postgres=> show search_path;

   search_path   

-----------------

 "$user", public

(1 row)



postgres=> set search_path=arun;

SET



postgres=> show search_path;

 search_path 

-------------

 arun

(1 row)

 

—Sequences: In Oracle, to  check a sequence value we use  “sequence_name.nextval” whereas in Postgres, we use “setval('sequence_name', value)”.

—Compound triggers are not supported in PostgreSQL.

—Oracle uses the function sysdate to get the current date and time in the server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in the session's timezone.

 

SYSDATE Function

In Oracle, the SYSDATE function returns date and time. PostgreSQL doesn't have any such function corresponding to the SYSDATE function.

Workaround: To achieve your requirement of returning date with time you can use any of the following functions:

1. now() or transaction_timestamp()

This function will give you the transaction start time.

2. statement_timestamp()

This function will give you the SQL start time.

3. clock_timestamp()

This function will give you the time when the function is implemented.

 

DELETE Syntax

In Oracle, the DELETE statement has two syntax options. That is, it can be used with a FROM clause and without a FROM clause. However, in PostgreSQL the FROM clause is mandatory.

Workaround: You will need to add a FROM clause in the Oracle DELETE statement.

Oracle example

SQL> delete from t1 where name='arun';

1 rows deleted.



SQL> delete t1 where name='arun';

1 rows deleted.

 

PostgreSQL example

postgres=# delete from t1 where name='arun';

2 rows deleted.

 

In PostgreSQL, a DELETE statement without a FROM clause will give you a syntax error:

postgres=# delete t1 where name='arun';

ERROR:  syntax error at or near "t1" at character 8

STATEMENT:  delete t1 where name='arun';

 

SUBSTR Function

The SUBSTR Function behaves differently in Oracle and PostgreSQL, which can lead to potential issues.

In Oracle:

SQL> select SUBSTR ('XYZ',-1) from dual;



S

-

Z

 

In PostgreSQL:

postgres=# select SUBSTR ('XYZ',-1);

 substr 

--------

 XYZ

(1 row)

 

Workaround: You can use the Oracfe SUBSTR function, which returns the same result in PostgreSQL.

 

Data migration 

Once the Oracle schema and code with the required changes are present in the PostgreSQL, the last step remains to migrate the data. This is a simple task compared the first two steps and requires time for copying data only. Usually, the time required is dependent on the underlying resources.  

Tools that can be used for migration

You can use an EnterpriseDB Migration Toolkit (MTK) and the EDB Migration Portal. For more information on these tools you can refer to the EDB user guides:

https://www.enterprisedb.com/edb-docs/d/edb-postgres-migration-toolkit/user-guides/user-guide/53.0.0/index.html

https://www.enterprisedb.com/edb-docs/d/edb-postgres-migration-portal/user-guides/user-guide/2.5.0/index.html

 

There are also external tools available for migration:

Ora2pg, which is an open source tool: http://ora2pg.darold.net/

Oracle to Postgres: https://www.convert-in.com/ora2pgs.htm

ora_migrator: https://github.com/cybertec-postgresql/ora_migrator

 

Arun GavhaneSenior Systems Engineer