10 Oracle to PostgreSQL Migration Challenges Solved

Every Oracle to PostgreSQL migration starts with a plan — just like the 14-month Java monolith migration we learned the hard way. You will convert the schema, dump the data, translate the stored procedures, test, cut over. Three weeks, maybe four. A clean swap. But Oracle to PostgreSQL migration challenges hide beneath every column type and procedural language construct.

That plan survives contact with the first Oracle package that references another Oracle package that references another. Then you discover that the DATE column you migrated holds the wrong century for a quarter of your records because Oracle DATE includes time and PostgreSQL DATE does not, and your validation queries used row counts instead of checksums.

We migrated a 400GB Oracle database to PostgreSQL for a client. The schema conversion took two weeks. The data migration took four days. Finding and fixing every silent data corruption issue took another two months. This article covers the specific Oracle to PostgreSQL migration challenges we did not expect — the ones that slip past schema validators and pass row-count checks but break your application in production. If you are planning Oracle to PostgreSQL migration work, these are the problems your migration tool will not warn you about.

Oracle to PostgreSQL Migration Challenge 1: Empty Strings Are Not NULL

The first Oracle to PostgreSQL migration challenge is that Oracle treats an empty string as NULL. PostgreSQL does not. If your application inserts '' into a VARCHAR column and then checks WHERE column IS NULL, it works on Oracle and breaks on PostgreSQL.

1-- Oracle: INSERT succeeds, '' becomes NULL
2INSERT INTO users (email) VALUES ('');
3SELECT * FROM users WHERE email IS NULL; -- returns the row
4
5-- PostgreSQL: INSERT succeeds, '' stays ''
6-- SELECT * FROM users WHERE email IS NULL; -- does NOT return the row
7-- SELECT * FROM users WHERE email = ''; -- returns the row

The fix is a text search across your entire application for patterns like IS NULL on string columns, and any ORM mapping that treats empty strings as null. Hibernate applications are especially vulnerable because different dialect settings handle this differently. This is one of the Oracle to PostgreSQL migration challenges that application-level testing often misses, and it is the first of many Oracle to PostgreSQL migration challenges that require application code changes rather than just database changes.

Oracle to PostgreSQL Migration Challenge 2: DATE vs TIMESTAMP

Another common Oracle to PostgreSQL migration challenge: Oracle's DATE data type stores both date and time. PostgreSQL's DATE stores only the date. If you migrate an Oracle DATE column to PostgreSQL DATE, you lose the time component silently. No error. No warning. Just truncated data.

1-- Oracle: DATE includes time
2CREATE TABLE orders (created_date DATE);
3INSERT INTO orders (created_date) VALUES (TO_DATE('2024-03-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS'));
4SELECT created_date FROM orders; -- 15-MAR-24 14:30:00
5
6-- Naive PostgreSQL migration:
7CREATE TABLE orders (created_date DATE);
8-- Query shows: 2024-03-15 -- time is GONE

The correct mapping is Oracle DATE to PostgreSQL TIMESTAMP(0). But here is the trap that makes this an Oracle to PostgreSQL migration challenge: your migration tool may map DATE to DATE because they share a name. You have to explicitly override every DATE column mapping. This class of Oracle to PostgreSQL migration challenges — where semantically different types share a name — requires a column-by-column schema review.

Oracle to PostgreSQL Migration Challenge 3: NUMBER Without Precision

This Oracle to PostgreSQL migration challenge hits when Oracle's NUMBER type without specified precision stores values exactly as entered, up to 38 digits. PostgreSQL's NUMERIC (without precision) behaves similarly but has subtle performance differences. The real trap is NUMBER columns that applications treat as integers or floats. Documenting every NUMBER column's actual precision requirements is the only way to solve Oracle to PostgreSQL migration challenges around numeric types.

1-- Oracle: NUMBER maps to what?
2CREATE TABLE products (price NUMBER);
3INSERT INTO products (price) VALUES (29.99);
4
5-- PostgreSQL options:
6-- NUMERIC(10,2) -- exact but you must guess precision
7-- DOUBLE PRECISION -- faster but loses decimal precision
8-- NUMERIC -- exact but slower for large datasets

If your migration tool maps NUMBER to DOUBLE PRECISION, 29.99 becomes 29.990000000000002. Row-count validation passes. Checksums fail. The application displays $29.990000000000002 instead of $29.99. We caught this one because a QA tester noticed a third decimal place that should not exist — pure luck. The Oracle to PostgreSQL migration challenges around data types are the ones most likely to cause silent corruption.

Oracle to PostgreSQL Migration Challenge 4: PL/SQL Packages Have No Equivalent

The trickiest Oracle to PostgreSQL migration challenge: Oracle packages group related procedures, functions, variables, and types into a single namespace. PostgreSQL has no equivalent concept. You cannot create a package with package-scoped variables in PostgreSQL. Among all the Oracle to PostgreSQL migration challenges we list here, this one requires the most manual effort.

1-- Oracle package with state:
2CREATE OR REPLACE PACKAGE order_api IS
3  g_discount NUMBER := 0.1;
4  PROCEDURE apply_discount(order_id NUMBER);
5END;
6
7-- PostgreSQL: no packages.
8-- Group functions in a schema, but package-scoped variables
9-- must be replaced with session-level parameters or erased entirely.

Every Oracle package must be exploded into individual functions and schemas. If the package uses package-scoped variables to maintain state across procedure calls, you must redesign the logic entirely because PostgreSQL session-level variables behave differently. This Oracle to PostgreSQL migration challenge alone can add weeks to the timeline for large codebases with heavy package usage.

Oracle to PostgreSQL Migration Challenge 5: CONNECT BY Must Become Recursive CTEs

Yet another Oracle to PostgreSQL migration challenge: Oracle's START WITH ... CONNECT BY syntax for hierarchical queries is concise and familiar to any Oracle developer. PostgreSQL uses WITH RECURSIVE, which is more verbose and behaves differently in edge cases.

1-- Oracle:
2SELECT employee_id, manager_id, LEVEL
3FROM employees
4START WITH manager_id IS NULL
5CONNECT BY PRIOR employee_id = manager_id;
6
7-- PostgreSQL:
8WITH RECURSIVE employee_tree AS (
9  SELECT employee_id, manager_id, 1 AS level
10  FROM employees
11  WHERE manager_id IS NULL
12  UNION ALL
13  SELECT e.employee_id, e.manager_id, et.level + 1
14  FROM employees e
15  JOIN employee_tree et ON e.manager_id = et.employee_id
16)
17SELECT * FROM employee_tree;

The recursive CTE equivalent is correct for standard hierarchies, but Oracle's CONNECT BY handles cycles differently and supports features like CONNECT_BY_IS_CYCLE and SYS_CONNECT_BY_PATH that have non-trivial PostgreSQL equivalents. This is one of those Oracle to PostgreSQL migration challenges that looks straightforward until you test it.

Oracle to PostgreSQL Migration Challenge 6: Outer Join Syntax

This Oracle to PostgreSQL migration challenge is the most famous Oracle-specific SQL quirk. Oracle's (+) outer join syntax is not supported by PostgreSQL and never will be. Every (+) join must be rewritten as a standard LEFT JOIN or RIGHT JOIN.

1-- Oracle:
2SELECT e.name, d.dept_name
3FROM employees e, departments d
4WHERE e.dept_id = d.dept_id(+);
5
6-- PostgreSQL:
7SELECT e.name, d.dept_name
8FROM employees e
9LEFT JOIN departments d ON e.dept_id = d.dept_id;

Most migration tools handle this conversion automatically. The Oracle to PostgreSQL migration challenge with joins is that Oracle's (+) has subtle differences from standard outer joins when used with multiple tables and complex WHERE conditions — specifically around row preservation in multi-table joins. Review every converted query for correctness rather than assuming the tool handled it. These Oracle to PostgreSQL migration challenges around syntax differences require the most thorough code review.

Oracle to PostgreSQL Migration Challenge 7: Sequences and Identity Columns

This Oracle to PostgreSQL migration challenge arises because Oracle uses sequences created independently from tables, referenced with sequence.NEXTVAL. PostgreSQL supports both standalone sequences and GENERATED AS IDENTITY columns. The set of Oracle to PostgreSQL migration challenges around identity management is broader than most teams expect.

1-- Oracle:
2CREATE SEQUENCE order_seq START WITH 1000;
3INSERT INTO orders (id) VALUES (order_seq.NEXTVAL);
4
5-- PostgreSQL standalone sequence:
6CREATE SEQUENCE order_seq START WITH 1000;
7INSERT INTO orders (id) VALUES (nextval('order_seq'));
8
9-- PostgreSQL identity column (recommended):
10CREATE TABLE orders (
11  id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1000)
12);

The unexpected Oracle to PostgreSQL migration challenge with sequences: if your Oracle sequence had gaps because of rollbacks, and your PostgreSQL migration resets the sequence without accounting for the maximum existing value, you can get primary key collisions on insert. Always set the PostgreSQL sequence start value to SELECT MAX(id) + 1 FROM migrated_table rather than copying the Oracle sequence's current value.

Oracle to PostgreSQL Migration Challenge 8: SYSDATE vs NOW()

An Oracle to PostgreSQL migration challenge your schema migration tool will not catch: Oracle's SYSDATE returns the current date and time. PostgreSQL has NOW() and CURRENT_TIMESTAMP, but the difference is that NOW() returns the transaction start time while CLOCK_TIMESTAMP() returns the actual current time. If your application relies on SYSDATE within a long-running transaction expecting different values on each call, changing to NOW() changes behavior. This is one of those Oracle to PostgreSQL migration challenges that only surfaces in production under real transaction loads.

1-- Oracle:
2INSERT INTO audit_log (event_time) VALUES (SYSDATE);
3
4-- PostgreSQL options:
5INSERT INTO audit_log (event_time) VALUES (NOW());
6-- NOW() returns transaction start time, same for entire transaction
7
8INSERT INTO audit_log (event_time) VALUES (CLOCK_TIMESTAMP());
9-- CLOCK_TIMESTAMP() returns actual current time, changes on each call

The Orafce compatibility extension provides a SYSDATE function that matches Oracle behavior, which can ease migration. But relying on compatibility layers long-term hides the underlying difference from your development team.

Oracle to PostgreSQL Migration Challenge 9: Stored Procedure Exception Handling

This Oracle to PostgreSQL migration challenge surfaces because PL/SQL and PL/pgSQL exception handling look similar on the surface but differ in critical ways. In Oracle, you can have multiple WHEN clauses in an exception block. In PostgreSQL, you have a single WHEN block. Many Oracle to PostgreSQL migration challenges stem from PL/SQL to PL/pgSQL translation gaps like this.

1-- Oracle PL/SQL:
2BEGIN
3  UPDATE inventory SET quantity = quantity - 1 WHERE product_id = p_id;
4EXCEPTION
5  WHEN NO_DATA_FOUND THEN
6    INSERT INTO error_log VALUES ('Product not found');
7  WHEN OTHERS THEN
8    ROLLBACK;
9    RAISE;
10END;
11
12-- PostgreSQL PL/pgSQL:
13BEGIN
14  UPDATE inventory SET quantity = quantity - 1 WHERE product_id = p_id;
15EXCEPTION
16  WHEN OTHERS THEN
17    -- Must check SQLSTATE to determine error type
18    IF SQLSTATE = 'P0002' THEN
19      INSERT INTO error_log VALUES ('Product not found');
20    ELSE
21      ROLLBACK;
22      RAISE;
23    END IF;
24END;

The bigger surprise: in PostgreSQL, when an exception occurs inside a BEGIN...EXCEPTION block, a savepoint is created automatically. This has performance implications for code that throws and catches exceptions frequently — each exception block adds overhead even when no exception occurs.

Oracle to PostgreSQL Migration Challenge 10: pgloader and Migration Tool Limitations

The final Oracle to PostgreSQL migration challenge is the tools themselves. Pgloader handles basic table creation and data transfer well but has significant gaps.

• Oracle-specific data types: RAW, BINARY_FLOAT, XMLTYPE require custom mapping
• Stored procedures and packages: Pgloader does not translate PL/SQL
• Sequences: Does not automatically set correct start values
• Large objects: BLOB migration with pgloader can fail on records over 1GB
• Character set issues: Oracle's AL32UTF8 to PostgreSQL UTF8 is not always clean

We used pgloader for the initial data dump and reasoned on top of it. The tool saved us weeks on the data transfer. But the schema conversion required manual intervention for every table with Oracle-specific types, and the stored procedure translation was entirely manual. This is the reality of Oracle to PostgreSQL migration work — tools handle the easy parts, you handle the hard parts.

Validate your migration with column-level checksums, not just row counts. We created a validation script that computed hash aggregates per column across both databases. It caught five data discrepancies that row-count matching missed — including one where a CLOB column silently truncated at 4000 characters because of an implicit VARCHAR conversion.

Validating Oracle to PostgreSQL Migration Challenges

After our first migration attempt caught only the obvious errors, we built a validation script that compared every column value between Oracle and PostgreSQL using hash aggregation. The approach: for each table, compute MD5(column_name::text) for every row, aggregate into a single checksum per column, and compare across databases.

1-- PostgreSQL validation query:
2SELECT
3  MD5(STRING_AGG(COALESCE(email::text, '∅'), '|' ORDER BY id)) AS email_hash,
4  MD5(STRING_AGG(COALESCE(status::text, '∅'), '|' ORDER BY id)) AS status_hash
5FROM users;

This caught the empty-string-as-NULL discrepancy on the first run. It caught the DATE truncation on the second. It caught the NUMBER precision loss on the third. Run this before cutover, not after — and pair it with the expand-contract database migration pattern to avoid locking your tables during the switch. The Oracle to PostgreSQL migration challenges in this article are the ones we actually hit. Your migration will have different ones, but the validation approach stays the same.

If you are planning an Oracle to PostgreSQL migration right now and staring at a schema with hundreds of tables and thousands of lines of PL/SQL, the advice is the same we give every client: budget twice the time for validation that you budgeted for migration. The data moves fast. The truth takes longer to verify. The Oracle to PostgreSQL migration challenges described here are the ones that cost us real production incidents. Every team discovers its own set of Oracle to PostgreSQL migration challenges during their first migration. The goal is to minimize surprises by knowing what to look for — and empty strings, DATE truncation, NUMBER precision, packages, CONNECT BY, outer joins, sequences, SYSDATE, exception handling, and tool gaps are the first ten to watch for.