Oracle to PostgreSQL: Technical quick guide

Data Type Mapping

The most visible difference when moving structures from Oracle to PostgreSQL is that PostgreSQL offers a far broader set of data types. Choosing the right type in the target system has a direct effect on query performance, so the mapping decision deserves more thought than a mechanical one-to-one translation.

A typical case is Oracle’s number(10, 0). It can be mapped to numeric(10, 0), but PostgreSQL’s native int8 will usually serve you better. int8 always consumes eight bytes, yet arithmetic on native integers is considerably faster than on the arbitrary-precision numeric type.

For character data, Oracle’s varchar2 maps directly to varchar or text. If your applications frequently perform case-insensitive lookups — typical in web development — the citext extension is worth evaluating. It provides a case-insensitive text type that simplifies search logic at the schema level.

Binary large objects are another common concern. PostgreSQL supports the bytea type for storing raw binary data directly in a column. If you prefer not to store BLOBs that way, you can use PostgreSQL’s built-in BLOB interface instead.

Oracle Spatial users need not worry: PostGIS provides comparable geospatial functionality on PostgreSQL, without practical limitations for migration scenarios.

Synonyms and Dependency Tracking

Oracle’s CREATE SYNONYM command has no direct PostgreSQL equivalent, and an implementation proposed for PostgreSQL was rejected because it conflicts with the project’s architecture. There are practical workarounds:

• Synonyms for tables: create a view (SELECT * …)
• Synonyms for views: add a view on top (SELECT * …)
• Synonyms for functions or procedures: write wrapper functions
• Synonyms for materialized views: use views

The cleaner approach during migration is to remove synonyms entirely and rely on the search_path setting, which often eliminates the underlying need for synonyms and simplifies future development.

PostgreSQL enforces strict dependency tracking, which differs from Oracle’s behavior. Dropping a table in PostgreSQL automatically drops dependent views and other objects. Oracle permits invalid views to linger after underlying objects change, so you should plan to resolve such objects as part of the migration rather than carrying them over.

Data Migration Pitfalls

Moving data from Oracle correctly requires attention to data quality issues on the source side that tools cannot always fix automatically:

• Infinite numbers (positive and negative infinity) — these only map cleanly to PostgreSQL’s infinity when you use double precision
• Corrupted strings — invalid byte sequences for UTF-8, such as 0x80, appear more often than expected
• Zero bytes — invalid byte sequence 0x00 likewise needs pre-treatment

These issues typically require remediation on the Oracle side before migration begins. The migration tooling can address many problems automatically, but not all of them.

Oracle also imposes limits on how data can be read during a consistent migration. To keep a single, consistent snapshot, you will run a long transaction on Oracle. This raises the risk of ORA-01555 (“snapshot too old”) errors, which occur when undo data is overwritten before a long-running transaction finishes. Mitigate this in advance by provisioning sufficiently large UNDO tablespaces. Additionally, the OCI interface can bottleneck throughput when fetching large fields, a restriction that lives on the Oracle side and cannot be fully worked around in client code.

Tablespaces Are Optional in PostgreSQL

Oracle treats tablespaces as a core storage abstraction. PostgreSQL takes a different route, relying on the operating system’s POSIX semantics. There is little benefit in recreating an elaborate tablespace layout during migration. In modern cloud or Kubernetes-based deployments, tablespaces are mostly obsolete on PostgreSQL.

SQL Syntax Conversion

Several Oracle-specific constructs have direct PostgreSQL counterparts that are worth knowing before you start converting application code.

Outer Join Syntax

Oracle’s legacy outer-join operator (+) needs rewriting to standard SQL:

Oracle

SELECT b.col1, a.col2
FROM base_table b, attributes a
WHERE b.id=a.b_id(+);

becomes in PostgreSQL:

SELECT b.col1, a.col2
FROM base_table b
LEFT JOIN attributes a ON b.id = a.b_id;

Subselect Aliases

Oracle permits anonymous subselects in the FROM clause:

SELECT * FROM (SELECT * FROM some_table)

PostgreSQL requires an alias:

SELECT * FROM (SELECT * FROM some_table) AS your_fancy_name

NULL Handling

Oracle treats NULL as an empty string, which changes concatenation behavior. In Oracle, 'hello' || NULL yields 'hello', whereas PostgreSQL returns NULL. To preserve Oracle semantics in PostgreSQL, use concat('hello', NULL) or guard columns with coalesce(strcol, ''). These subtle differences often hide bugs, so inspect every string operation that might involve NULL.

Date and Time Functions

The common Oracle calls SYSDATE and SYSTIMESTAMP translate to clock_timestamp(), current_date, or current_timestamp in PostgreSQL. These replacements are straightforward enough for a global search-and-replace.

Sequences

Both databases use proprietary syntax rather than the ANSI standard NEXT VALUE FOR asequence:

• Oracle: asequence.NEXTVAL
• PostgreSQL: nextval('asequence')

DUAL Is Unnecessary

PostgreSQL does not require a FROM clause, so there is no need for SELECT * FROM dual;. The orafce extension can emulate DUAL, but it is better to omit it entirely.

DECODE to CASE

Oracle’s decode(expr, search, result [, search, result...] [, default]) is easy to port. You can replace it with a standard CASE expression or use the decode function from orafce. Because DECODE lacks complex logic, a direct CASE translation works in most instances:

CASE WHEN expr THEN expr [...] ELSE expr END

CONNECT BY to WITH RECURSIVE

Oracle supports recursive queries through both the newer WITH RECURSIVE and the older CONNECT BY syntax. PostgreSQL has a full WITH RECURSIVE implementation and no equivalent to CONNECT BY. The Oracle form


SELECT empno, ename, level
FROM emp
START WITH empno = 7788
CONNECT BY PRIOR mgr = empno;

maps to the PostgreSQL recursive CTE:

WITH hierarchy (empno, ename, mgr, "level") AS (
SELECT empno, ename, mgr, 1 AS "level"
FROM emp
WHERE empno = 7788
UNION ALL
SELECT e.empno, e.ename, e.mgr, h."level" + 1
FROM hierarchy h
JOIN emp e ON h.mgr = e.empno
) SELECT empno, ename, "level" FROM hierarchy;

Porting Stored Code from Oracle to PostgreSQL

Stored procedure code is typically the most expensive part of any database migration. While PL/pgSQL is modeled on Oracle's PL/SQL, the two languages diverge in enough details—such as RETURNS versus RETURN—that every port requires careful review. Automatic conversion tools can handle much of the mechanical rewriting, but the semantic gaps need manual attention.

If your Oracle code relies on PL/Java, PostgreSQL has a server-side Java implementation, but it is rarely used in practice. In most migration projects, the better move is to replace PL/Java code entirely with a PostgreSQL-native language.

Handling Autonomous Transactions

PostgreSQL does not support PRAGMA AUTONOMOUS_TRANSACTION. The usual workaround is to restructure the logic so that a savepoint can serve the same purpose and the transaction can commit normally. In the few cases where that is impossible, the dblink extension can open a separate connection and run the work in an independent transaction.

BULK COLLECT vs. Row-by-Row Processing

There is no direct equivalent of Oracle's BULK COLLECT in PostgreSQL. The natural PostgreSQL style is row-by-row processing, but this can hurt performance. When porting code that relies on bulk operations, it is often better to rewrite the logic as a single SQL statement rather than translate the loop construct directly.

Oracle-Compatible Functions via orafce

PostgreSQL frequently offers the same functionality as Oracle, but under different function names. The orafce extension closes that gap by implementing many Oracle-specific functions natively in PostgreSQL. This reduces the number of manual code changes required during migration. The extension has been maintained for years and is stable enough to rely on in production.

Examples of the compatible functions it exposes include:

SELECT last_day('2000-02-01 121143');
SELECT next_day ('2008-01-01 121212', 'sunAAA');
SELECT months_between ('2007-02-28 111111', '2007-04-30 112121');
select dbms_pipe.purge('bob');
select instr('Tech on the net', 'e') = 2;

Simulating PL/SQL Packages

PostgreSQL has no package concept like Oracle's. In practice, this is less of a limitation than it first appears. Two common strategies replace packages cleanly:

• Bundle related functions into a PostgreSQL extension and load it with CREATE EXTENSION
• Use one schema per logical "package" to group the functions and data together

Either approach gives you the organizational benefits of packages without requiring a fundamental change in how the application calls the code.

Rewriting Triggers

In PostgreSQL, a trigger never contains inline code. Instead, the CREATE TRIGGER statement always points to a separately defined function:

demo=# \h CREATE TRIGGER
Command:     CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ]
}
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ]
]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

Where event can be one of:

INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE

This separation has a real advantage: the trigger function is an ordinary function and can be called directly or reused by multiple triggers. It also means that triggers used solely to increment a sequence are unnecessary—a DEFAULT column clause can assign the next value automatically.

One feature that does not carry over is Oracle's LOGON trigger. PostgreSQL offers no equivalent; if you need that kind of session-level logic, it has to move into the application layer or be removed altogether.