Oracle to PostgreSQL Migration Challenges: 11 Traps and Their PostgreSQL Equivalents

The short answer

The Oracle to PostgreSQL migration issues that cause production incidents are the ones where both databases accept the same SQL and return different answers. The biggest are empty strings (Oracle treats '' as NULL, PostgreSQL does not), DATE (Oracle's carries a time, PostgreSQL's does not), and NUMBER columns mapped to the wrong type. Next come the features PostgreSQL implements differently or not at all: packages, CONNECT BY, (+) outer joins, autonomous transactions, synonyms and optimizer hints. Each has a known equivalent, listed below with the PostgreSQL feature that replaces it.

The step-by-step Oracle to PostgreSQL migration guide shows where each fix lands in the project, and PL/SQL to PL/pgSQL conversion covers the procedural code.

1. Empty string versus NULL

Oracle's SQL reference says the database currently treats a character value with a length of zero as null, and recommends that you do not rely on it. Applications rely on it anyway. In Oracle, inserting '' stores NULL, WHERE col = '' matches nothing, and WHERE col IS NULL finds the rows written with ''. Concatenation also differs: in Oracle, 'abc' || NULL returns 'abc'. In PostgreSQL, '' is a real value distinct from NULL, and || with a NULL operand returns NULL.

Pick one representation per column and enforce it. Converting empty strings to NULL during the data load matches Oracle's stored data most closely. orafce ships oracle.replace_empty_strings(), a trigger function that turns '' into NULL on insert and update. For concatenation, PostgreSQL's concat() ignores NULL arguments, which reproduces the Oracle result:

-- Oracle: 'Smith' || NULL  returns 'Smith'
SELECT concat(last_name, suffix) FROM customers;

-- Keep '' out of a column, as Oracle would
CREATE TRIGGER customers_no_empty
  BEFORE INSERT OR UPDATE ON customers
  FOR EACH ROW EXECUTE FUNCTION oracle.replace_empty_strings();

ora2pg can also rewrite IS NULL tests in converted code to treat empty strings as NULL, through its NULL_EQUAL_EMPTY setting. Its own documentation says normalising the data is the better fix when you control the application.

2. DATE and TIMESTAMP semantics

Oracle DATE contains year, month, day, hour, minute and second, with no fractional seconds and no time zone. PostgreSQL date has no time of day. Mapping one to the other silently drops every time value, so map Oracle DATE to timestamp(0) unless you have checked that a column only ever holds midnight.

Date arithmetic changes with it. In Oracle, hire_date + 1 adds a day and subtracting two dates returns a number of days. In PostgreSQL, add an interval to a timestamp, and subtracting two timestamps returns an interval:

-- Oracle: SYSDATE + 1, end_date - start_date
SELECT localtimestamp(0) + interval '1 day';
SELECT extract(epoch FROM end_ts - start_ts) / 86400 AS days FROM jobs;

orafce's oracle.date type keeps Oracle's arithmetic, including + and - with numbers, if a rewrite is out of reach. For zones, note that Oracle's TIMESTAMP WITH TIME ZONE keeps the zone it was given, while PostgreSQL's timestamptz stores the value as UTC and does not retain the original zone. If the application reads the zone back, store it in its own column.

3. NUMBER mapping

Oracle uses NUMBER for everything from flags to money. PostgreSQL's numeric holds the same values exactly, so it is the safe default for NUMBER(p,s). It is slower than the integer types, though, and joins on numeric keys cost more than joins on bigint. ora2pg's PG_INTEGER_TYPE setting maps NUMBER(p) to smallint, integer or bigint by precision, and maps bare NUMBER to bigint by default. That last rule is the trap: a bare NUMBER can hold decimals, and bigint rounds them away on load. Query the maximum scale of every bare NUMBER column before accepting the mapping. Avoid PG_NUMERIC_TYPE's mapping to real and double precision for money; the ora2pg documentation itself warns about rounding.

4. Sequences and identity columns

Oracle sequences map directly: CREATE SEQUENCE exists in PostgreSQL, and seq.NEXTVAL becomes nextval('seq'). Where Oracle tables fill keys from a sequence in a trigger, replace the trigger with a column default or an identity column, which PostgreSQL has had since version 10:

CREATE TABLE orders (
  order_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  placed_at timestamp(0) NOT NULL DEFAULT localtimestamp(0)
);

BY DEFAULT lets the data load insert the existing keys. After the load, move each sequence past the highest key with setval, or the first insert in production fails on a duplicate key.

5. PL/SQL packages

PostgreSQL has no packages. Its porting guide recommends schemas to group the functions, and points out the harder part: no package means no package-level variables. ora2pg creates one schema per package by default, so billing.post_invoice() still resolves. For package state, use a temporary table, as the porting guide suggests, or a custom session setting with set_config and current_setting. Package initialisation blocks and private routines need a design decision each, and the conversion patterns post shows both.

6. CONNECT BY to recursive CTEs

Oracle's hierarchical query clause walks parent and child rows with START WITH and CONNECT BY, and NOCYCLE stops loops. PostgreSQL uses the SQL standard WITH RECURSIVE. From PostgreSQL 14, the SEARCH DEPTH FIRST clause reproduces Oracle's depth-first order and the CYCLE clause marks loops:

-- Oracle
SELECT employee_id, manager_id, LEVEL
  FROM employees
 START WITH manager_id IS NULL
 CONNECT BY PRIOR employee_id = manager_id;

-- PostgreSQL 14 and later
WITH RECURSIVE org AS (
  SELECT employee_id, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, o.level + 1
    FROM employees e JOIN org o ON e.manager_id = o.employee_id
) SEARCH DEPTH FIRST BY employee_id SET ord
  CYCLE employee_id SET is_cycle USING path
SELECT employee_id, manager_id, level FROM org ORDER BY ord;

On PostgreSQL 13 and earlier, build the path as an array in the recursive term and sort on it. SYS_CONNECT_BY_PATH becomes a string or array built the same way.

7. (+) outer joins

Oracle's (+) operator marks the optional side of an outer join in the WHERE clause, and Oracle itself recommends the ANSI OUTER JOIN syntax instead. PostgreSQL supports only the ANSI form. ora2pg rewrites simple cases. Rewrite the rest by hand, and watch filters on the optional table: a condition that sat in the WHERE clause with (+) belongs in the ON clause, or the outer join turns into an inner one.

-- Oracle
SELECT c.name, o.total FROM customers c, orders o
 WHERE c.id = o.customer_id(+) AND o.status(+) = 'OPEN';

-- PostgreSQL
SELECT c.name, o.total FROM customers c
  LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'OPEN';

8. Autonomous transactions

Oracle's AUTONOMOUS_TRANSACTION pragma runs a routine in its own transaction, typically to write an audit or error log that survives a rollback of the caller. PostgreSQL has no such pragma. The two common workarounds both open a second session: the dblink extension, which ships with PostgreSQL, and the pg_background extension, which runs SQL in a background worker. ora2pg generates dblink wrappers by default and pg_background ones when its PG_BACKGROUND setting is on. Both cost a new connection or worker per call, so keep them out of hot paths, and consider whether an error log written after the transaction ends would do the same job.

9. Synonyms

An Oracle synonym is an alternative name for a table, view, sequence, procedure or other object, often used so applications can reference another schema's objects without a prefix. PostgreSQL has no synonyms. Set search_path on the application role so unqualified names resolve to the right schemas, and use views for the cases where the synonym renamed the object. ora2pg exports synonyms as views. Public synonyms over database links need a foreign data wrapper or a redesign.

10. Identifier case sensitivity

Oracle interprets unquoted identifiers as upper case, and PostgreSQL folds them to lower case. As long as nobody quotes names, SELECT Order_Id FROM Orders works on both. The trouble starts with quoted names: an application or ORM that sends "ORDERS" will not find a PostgreSQL table created as orders. Decide early whether to create everything in lower case and remove quotes from the application, or keep upper-case quoted names everywhere. Mixing the two is a common cause of "relation does not exist" errors in the first test run.

11. Optimizer hints

Oracle hints are comments that pass instructions to the optimizer. PostgreSQL treats them as ordinary comments and ignores them, and the community's position, recorded on the PostgreSQL wiki, is that it does not want hints implemented the way other databases implement them. Expect some queries that were hinted into a good plan on Oracle to plan differently. Run ANALYZE, check the plans with EXPLAIN (ANALYZE, BUFFERS), add the missing indexes, and rewrite queries that depended on a hint. The pg_hint_plan extension adds comment-based hints when a rewrite is not possible.

Other differences worth a test case

• Exceptions roll back the block. In PL/pgSQL, catching an exception rolls back all changes since the block's BEGIN, like an Oracle savepoint. Code that expected earlier statements to survive needs restructuring.
• ROWNUM. Replace with LIMIT or FETCH FIRST, or with row_number() where the number itself is used.
• MERGE. PostgreSQL has had MERGE since version 15. On older targets, use INSERT ... ON CONFLICT.
• Implicit conversions. Oracle converts between strings, numbers and dates more freely than PostgreSQL, so comparisons such as a number column against a quoted literal may need explicit casts.

Where OSSeva fits

OSSeva Assure includes Oracle-to-Postgres migration design with query compatibility analysis, which is where these traps are found before they reach production. After cutover, OSSeva for PostgreSQL supports the cluster you landed on, including versions past community end of life, under one contract priced per cluster. See Oracle to PostgreSQL with OSSeva.

Frequently asked questions

What are the biggest Oracle to PostgreSQL migration challenges?

The empty string rule, DATE columns that carry a time, NUMBER columns mapped to the wrong type, and the volume of PL/SQL in packages. The first three corrupt data quietly; the last one sets the timeline.

What Oracle to PostgreSQL migration issues show up after go-live?

Queries that relied on '' being NULL, sequences that were not reset after the data load, plans that changed when hints stopped working, and quoted identifiers in code paths the tests did not reach.

Does PostgreSQL support CONNECT BY?

No. Use WITH RECURSIVE. PostgreSQL 14 and later add SEARCH and CYCLE clauses that cover ordering and loop detection.

Does PostgreSQL have packages or synonyms?

Neither. Packages become schemas, with state in temporary tables or session settings. Synonyms become views or a search_path setting.

How do you handle autonomous transactions in PostgreSQL?

With dblink or the pg_background extension, which run the statement in a separate session. ora2pg can generate the wrappers for you.