advanced 1-6 months depending on PL/SQL complexity100% Free Guide

How to Migrate from Oracle to PostgreSQL

Oracle to PostgreSQL is one of the most complex database migrations due to Oracle's extensive use of PL/SQL, Oracle-specific SQL extensions, and proprietary features like CONNECT BY hierarchical queries. The Ora2Pg tool is specifically designed for this migration path.

9

Data Type Mappings

8

Syntax Differences

6

Migration Steps

3

Free Tools

Data Type Mappings

OraclePostgreSQLNotes
NUMBER(p,s)NUMERIC(p,s)NUMERIC is the equivalent; for integer use INTEGER or BIGINT
VARCHAR2(n)VARCHAR(n)—
CLOBTEXT—
BLOBBYTEA—
DATE (includes time)TIMESTAMPOracle DATE includes time; map to TIMESTAMP
TIMESTAMP WITH TIME ZONETIMESTAMPTZ—
ROWIDUse CTID (hidden system column) or OIDDifferent internal row identifier concept
SEQUENCESEQUENCE (same concept)Syntax slightly different: CREATE SEQUENCE name
NCHAR / NVARCHAR2CHAR / VARCHARPostgreSQL is UTF-8 by default

SQL Syntax Differences

Hierarchical Queries

Oracle

CONNECT BY PRIOR / START WITH

PostgreSQL

Recursive CTEs (WITH RECURSIVE)

Major rewrite — CONNECT BY has no direct PostgreSQL equivalent

Dual table

Oracle

SELECT SYSDATE FROM DUAL

PostgreSQL

SELECT NOW() (no FROM required)

PostgreSQL doesn't need FROM for scalar expressions

Sequences

Oracle

seq_name.NEXTVAL, seq_name.CURRVAL

PostgreSQL

NEXTVAL('seq_name'), CURRVAL('seq_name')

Function-call syntax instead of dot notation

Outer Join (+)

Oracle

WHERE t1.id = t2.id(+)  (Oracle outer join)

PostgreSQL

LEFT JOIN t2 ON t1.id = t2.id

Oracle (+) syntax must be rewritten as ANSI JOIN

ROWNUM

Oracle

WHERE ROWNUM <= 10

PostgreSQL

LIMIT 10

ROWNUM is Oracle-specific; use LIMIT in PostgreSQL

NVL

Oracle

NVL(col, 'default')

PostgreSQL

COALESCE(col, 'default')
DECODE

Oracle

DECODE(col, val1, res1, val2, res2, default)

PostgreSQL

CASE WHEN col = val1 THEN res1 WHEN col = val2 THEN res2 ELSE default END
SYSDATE

Oracle

SYSDATE

PostgreSQL

NOW() or CURRENT_TIMESTAMP

Step-by-Step Migration Guide

1

Assess with Ora2Pg

Run Ora2Pg in 'SHOW_REPORT' mode to get a migration complexity score for each object type (tables, packages, procedures, triggers).

ora2pg -t SHOW_REPORT --estimate_cost -c config/ora2pg.conf
2

Extract and Convert Schema

Use Ora2Pg to automatically convert Oracle DDL, sequences, and basic stored procedures to PostgreSQL format.

ora2pg -t TABLE -o tables.sql -c config/ora2pg.conf
ora2pg -t SEQUENCE -o sequences.sql -c config/ora2pg.conf
3

Port PL/SQL Packages to PL/pgSQL

Oracle PL/SQL packages (groups of procedures and functions) must be converted to PostgreSQL schemas with individual PL/pgSQL functions. This is usually the largest effort.

4

Replace Oracle-specific SQL

Replace CONNECT BY with recursive CTEs, ROWNUM with LIMIT, (+) outer join syntax with ANSI JOINs, DUAL table references, and Oracle functions (NVL, DECODE, TRUNC, etc.).

5

Migrate Data

Use Ora2Pg for data export or Oracle's Data Pump with PostgreSQL's COPY for large tables.

ora2pg -t COPY -o data.sql -c config/ora2pg.conf
6

Test and Validate

Compare row counts, spot-check business-critical data, run all application tests, and use EXPLAIN ANALYZE to validate query performance.

Common Issues & Solutions

CONNECT BY hierarchical queries

Convert to recursive CTEs: WITH RECURSIVE cte AS (anchor SELECT UNION ALL recursive SELECT ... FROM cte JOIN table) SELECT FROM cte. This requires understanding the hierarchy structure and rewriting the traversal logic.

Oracle PL/SQL packages

Create a PostgreSQL schema with the same name as the package. Convert each procedure/function in the package to a PostgreSQL function within that schema. Package-level variables must be converted to function parameters or session-level variables.

Oracle-specific functions with no PostgreSQL equivalent

LPAD/RPAD, SUBSTR, INSTR work the same. DECODE → CASE WHEN. NVL → COALESCE. TRUNC(date) → DATE_TRUNC('day', date). TO_DATE → TO_TIMESTAMP. SYSDATE → NOW().

Oracle triggers with WHEN clause and predicates

PostgreSQL triggers don't support WHEN on row values in the same way. Move WHEN logic inside the trigger function body using IF statements.

Recommended Migration Tools

Ora2Pg

FREE

The standard open-source tool for Oracle to PostgreSQL migration. Converts schema, data, and stored procedures.

AWS Schema Conversion Tool

FREE

Automated PL/SQL to PL/pgSQL conversion with complexity scoring.

pgAdmin

FREE

PostgreSQL administration and query tool.

Frequently Asked Questions

What is Ora2Pg and how does it help migrate Oracle to PostgreSQL?
Ora2Pg is an open-source Perl tool specifically designed for Oracle to PostgreSQL migration. It connects to an Oracle database and exports: table structures (with data type conversion), sequences, views, stored procedures and functions, triggers, and data. It provides a migration complexity score (SHOW_REPORT mode) to estimate migration effort. It handles most routine conversions automatically, leaving complex PL/SQL for manual porting.
How do you convert Oracle CONNECT BY to PostgreSQL?
Oracle CONNECT BY has no direct equivalent in PostgreSQL. Replace with a recursive CTE: Oracle: SELECT emp_id, name, manager_id FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR emp_id = manager_id. PostgreSQL: WITH RECURSIVE org AS (SELECT emp_id, name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.name, e.manager_id FROM employees e JOIN org ON e.manager_id = org.emp_id) SELECT * FROM org;

Ready to Migrate?

Test your PostgreSQL queries in our free SQL Playground before migrating.

Test in Playground All Migration Guides