	In Browser
	StumbleUpon
	del.icio.us
	Google
	Google Buzz
	reddit
	LinkedIn

	Facebook
	Twitter
	Linkedin
	E-Mail

Data Migration > Database Migration > Oracle to PostgreSQL Migration

Oracle to PostgreSQL Migration

Author: Venkata Sudhakar

Oracle to PostgreSQL is one of the most common enterprise database migrations, driven by licensing cost savings and cloud-native adoption. PostgreSQL is highly capable and covers the vast majority of Oracle use cases, but the two databases have meaningful differences in SQL dialect, data types, and procedural language that require careful conversion work. The data migration itself - moving rows from Oracle to PostgreSQL - is the easy part. The hard part is converting stored procedures, functions, triggers, and Oracle-specific SQL syntax to their PostgreSQL equivalents.

The AWS Schema Conversion Tool (SCT) and ora2pg are the two most popular tools for automating this conversion. SCT produces a report showing which objects it can convert automatically and which require manual review, flagging Oracle-specific features like CONNECT BY hierarchical queries, ROWNUM, Oracle sequences, and PL/SQL syntax that has no direct PostgreSQL equivalent. Expect 60-80% automatic conversion and 20-40% requiring manual rewriting for a typical enterprise schema.

The below example shows the most common Oracle-to-PostgreSQL syntax differences you will encounter in SQL and stored procedure conversion.

-- Common Oracle to PostgreSQL SQL conversions

-- 1. ROWNUM (Oracle) -> LIMIT (PostgreSQL)
-- Oracle: SELECT * FROM orders WHERE ROWNUM <= 10;
SELECT * FROM orders LIMIT 10;

-- 2. SYSDATE (Oracle) -> CURRENT_TIMESTAMP (PostgreSQL)
-- Oracle: SELECT SYSDATE FROM DUAL;
SELECT CURRENT_TIMESTAMP;

-- 3. NVL (Oracle) -> COALESCE (PostgreSQL)
-- Oracle: SELECT NVL(phone, "N/A") FROM customers;
SELECT COALESCE(phone, "N/A") FROM customers;

-- 4. Sequences: Oracle uses CREATE SEQUENCE + .NEXTVAL
-- Oracle: INSERT INTO orders (id) VALUES (order_seq.NEXTVAL);
-- PostgreSQL: use SERIAL or IDENTITY column instead
CREATE TABLE orders (
    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount  NUMERIC(10,2)
);
-- Or use a sequence explicitly:
CREATE SEQUENCE order_seq START 1;
INSERT INTO orders (id) VALUES (nextval("order_seq"));

-- 5. VARCHAR2 (Oracle) -> VARCHAR (PostgreSQL)
-- Oracle: name VARCHAR2(100);
-- PostgreSQL:
CREATE TABLE customers (
    name  VARCHAR(100),
    notes TEXT          -- TEXT has no length limit in PostgreSQL
);

Common PL/SQL to PL/pgSQL procedure conversion,

Key differences to watch for during migration,

Oracle           ->  PostgreSQL
----------------------------------
VARCHAR2         ->  VARCHAR or TEXT
NUMBER(10,2)     ->  NUMERIC(10,2)
DATE             ->  TIMESTAMP (Oracle DATE includes time!)
SYSDATE          ->  CURRENT_TIMESTAMP
NVL()            ->  COALESCE()
DECODE()         ->  CASE WHEN ... END
ROWNUM           ->  LIMIT / OFFSET
CONNECT BY       ->  WITH RECURSIVE (CTE)
sequence.NEXTVAL ->  nextval("sequence")
DUAL table       ->  Not needed (omit FROM DUAL)
PL/SQL           ->  PL/pgSQL (similar but different)

Migration approach: use AWS DMS or GCP Datastream for the data movement (rows), and ora2pg or AWS SCT for the schema and code conversion. Run the two workstreams in parallel - convert and test the schema/procedures in a dev environment while DMS migrates data in the background. The biggest time investment is always stored procedure conversion and regression testing of application queries against the new database engine.

Send your comments, suggestions or queries regarding this site to [email protected].