An Oracle to PostgreSQL migration is often planned as a schema conversion. Tools for that exist and translate tables, indexes and most constraints quickly. The work that decides the deadline and the risk lies elsewhere: in data types that behave differently, in stored procedures, in SQL scattered across applications and in proving that the data matches after the transfer. This article is for architects, development leads and those who approve or budget a migration.

In 2022–2023 we migrated data from Oracle to PostgreSQL as part of a government information systems optimisation project, working as a subcontractor through a partner company. Below are the five places where we spent the most time, and the approach we recommend. The technical statements are based on the PostgreSQL and Oracle documentation. Verify the behaviour on your own versions.

Licence costs are a common reason to migrate, since PostgreSQL is released under the liberal open source PostgreSQL License. Licence savings are not the whole balance, though: against them stand the conversion and testing work, support that has to be secured differently, and the skills of a team that has so far known Oracle.

1. Data types that look the same

Empty strings were the most expensive item for us, because the application relied on them in dozens of places. According to its own documentation, Oracle treats a zero-length string as NULL (and recommends not relying on this), whereas PostgreSQL distinguishes the two. An application that writes an empty string stores a real value in PostgreSQL: an IS NULL condition will not find it, a NOT NULL constraint lets it through, and a unique index reports a conflict on the second empty string. Concatenation differs as well: in Oracle, concatenating a string with NULL returns the string, while in PostgreSQL the || operator returns NULL. Choose one rule, for example storing an empty string as NULL, and apply it in both data and code.

  • NUMBER without precision and scale means the maximum range in Oracle. The safe translation is unconstrained numeric, but according to the PostgreSQL documentation calculations on it are very slow compared to integer types. Where a column holds only integers within range (keys, counts), use bigint, and decide from the actual data, not from the column name.
  • Oracle DATE includes the time to the second, but PostgreSQL date has no time of day. A direct translation silently drops the time, so the target is usually timestamp. Also, SYSDATE returns the server’s current time, while now() in PostgreSQL returns the start time of the transaction.
  • VARCHAR2 length is in bytes or characters depending on the BYTE or CHAR qualifier, and without one on NLS_LENGTH_SEMANTICS. Varchar(n) in PostgreSQL counts characters, so application length validations that counted bytes need reviewing.
  • Both databases blank-pad CHAR, but PostgreSQL removes trailing spaces when converting to another string type and recommends text or varchar instead. Test comparisons of CHAR with VARCHAR.
  • CLOB and BLOB translate to text and bytea. A field value is limited to 1 GB in PostgreSQL, while an Oracle LOB can be larger. In that case you need large objects or storage outside the database.
  • Oracle TIMESTAMP WITH TIME ZONE keeps the zone or offset, while PostgreSQL timestamptz converts the value to UTC and does not retain the original zone. If the application needs it, it belongs in a separate column.

2. Sequences and identity columns

Sequences and identity columns can be carried over, but their values have to be set after the data transfer. The setval function sets the last issued value and the next nextval continues after it. With GENERATED … AS IDENTITY columns the trap is less visible: according to the documentation, COPY uses the values from the input regardless of the identity setting, so the attached sequence stays at the start. Forgetting this means primary key conflicts on the first day of operation. Setting sequences therefore belongs in the cutover script as a checked step, not among manual tasks. If the application inserts its own key values, the GENERATED ALWAYS variant rejects them without OVERRIDING SYSTEM VALUE.

3. Stored procedures

PL/SQL and PL/pgSQL look alike and differ in the details. Automatic conversion gives a skeleton, and the rest is manual work and tests:

  • PostgreSQL has no packages. Functions are grouped into schemas, and package variables (session state) need replacing, for example with temporary tables.
  • A block with EXCEPTION rolls back all changes since the start of the block when it catches an error, which matches the savepoint and ROLLBACK TO pattern in Oracle. According to the documentation it is significantly more expensive, so it does not belong in tight loops.
  • PL/pgSQL has no autonomous transactions, typically used for auditing or error logging. Workarounds through extensions (dblink, pg_background) open another connection. It is often cleaner to write such a log outside the database transaction.
  • %TYPE and %ROWTYPE work the same. BULK COLLECT and FORALL have no direct equivalent. They are usually replaced by a single set-based statement, not by a row-by-row loop.
  • Since PostgreSQL 11, COMMIT and ROLLBACK are possible in procedures invoked with CALL, but not in functions or in a block with EXCEPTION.

The orafce extension adds part of Oracle’s functions and packages (NVL, DECODE, the DUAL table, DBMS_OUTPUT and others). It speeds up conversion but adds a component that must be maintained through upgrades and supported by the target environment, for example a managed cloud database. Procedures nobody uses are not converted, and a migration is a good opportunity to find out which ones those are.

4. Applications with the dialect in their code

Searching the application code for Oracle dialect took more time than the database itself. A list of patterns and automated search helped, but each spot was decided manually.

  • ROWNUM is replaced by LIMIT or FETCH FIRST. But Oracle evaluates ROWNUM before the ORDER BY at the same query level, so the original query may have returned different rows than its author intended. A mechanical rewrite changes the result, so decide which one is correct.
  • NVL becomes COALESCE, DECODE becomes CASE. DECODE treats two NULLs as equal, but CASE x WHEN NULL never matches, so you need IS NOT DISTINCT FROM.
  • Rewrite (+) joins as LEFT or RIGHT JOIN. If (+) is missing from one of several conditions, Oracle silently returns an inner join, so the old query may have carried a hidden bug.
  • CONNECT BY is rewritten as WITH RECURSIVE, and LEVEL, the tree path and cycle protection have to be added by hand.
  • PostgreSQL supports MERGE from version 15. Version 14 is supported until 12 November 2026 and has no MERGE. The INSERT … ON CONFLICT alternative needs a unique index or constraint and behaves differently.
  • Core PostgreSQL ignores hints in comments. Verify the performance that depended on them with query plans at production volume.
  • Oracle folds unquoted identifiers to upper case, PostgreSQL to lower case. Code or an ORM that quotes names in upper case will not find the objects.

5. Transactions, errors and sorting

Oracle has statement-level atomicity: if a statement fails, for example on a duplicate key, only that statement is rolled back and the earlier work in the transaction remains. In PostgreSQL an error puts the whole transaction into an aborted state, and further statements are rejected until a ROLLBACK or a return to a savepoint. An application that caught a failed INSERT in Oracle and carried on needs a savepoint before the risky statement (the JDBC driver, for instance, can insert them automatically, at a cost in overhead) or different logic, such as INSERT … ON CONFLICT. Oracle, according to its documentation, first commits the transaction in progress and only then runs the DDL statement as a transaction of its own, which it also commits. If the DDL fails, the earlier changes remain committed. PostgreSQL normally supports transactional DDL, with exceptions such as CREATE DATABASE, which cannot run in a transaction block. Migration scripts therefore behave differently when they fail midway.

For text, collation decides. In Oracle, ORDER BY follows the NLS_SORT parameter and WHERE comparisons follow NLS_COMP, binary by default. In PostgreSQL the column or database collation applies to both sorting and comparisons. The result can be a different order with diacritics and upper case, and different pagination. Choose the collation explicitly when creating the database.

Tools and data verification

Ora2Pg (open source, GPL licence) scans an Oracle database and generates scripts for PostgreSQL, including procedures, packages and data export. Its report with a cost estimate is a good input to the plan, not a budget. Tools from cloud providers, for example AWS, play a similar role. None of them will decide for you what to do with empty strings or with ROWNUM before ORDER BY.

  • Row counts per table are the minimum, not proof.
  • Checksums over numeric columns (sum, minimum, maximum, NULL count) and hashes over text columns or samples reveal silent errors in encoding and precision. The hash must be built from an identically normalised representation on both sides (date format, decimal separator, NULL versus empty string), otherwise it measures a difference in formats.
  • Row-by-row comparison: Ora2Pg has a TEST_DATA mode, which needs a primary key or unique index if the data has changed in the meantime.
  • Comparing the results of the most common application queries and reports on both systems before the switch, including row order where it matters.

Cutover and rollback plan

Cutover is a scenario of its own: stopping writes to Oracle, the final transfer of changes, setting sequences, verifying data against criteria agreed in advance, application tests, and a go-live decision based on measurable criteria, not on gut feeling. It pays to run the whole scenario at least once as a timed dry run on a copy of production data.

The rollback plan must state until when you can go back and what happens to data already written to PostgreSQL. After cutover, leave Oracle untouched and read-only. As long as nobody writes to PostgreSQL, rolling back means switching connections. After the first production writes it means transferring changes back. That has to be prepared and tested in advance, or you consciously accept that from a certain point there is only forward. The system owner should decide this before cutover.

Pre-migration checklist

  • An inventory of objects and their actual use: what nobody calls is not migrated.
  • A list of applications, interfaces, reports and batch jobs that access the database.
  • A written and agreed rule for empty strings and NULL.
  • Column-by-column type mapping, verified on real data.
  • Encoding and collation of the target database.
  • A target PostgreSQL version supported for the planned operating period (at least 15 if you need MERGE).
  • A decision on orafce and on whether the target environment supports it.
  • A script setting sequences as part of cutover.
  • Verification criteria and reference queries with expected results.
  • Performance tests of key queries at production volume.
  • A cutover rehearsal and a rollback plan with the point after which it is no longer just a connection switch.

The migration succeeded, but a plan that counts only on schema conversion underestimates the work several times over. We recommend starting with an inventory and the tool’s report, deciding the data type rules before anyone starts rewriting code, and preparing how you will prove the data matches before the first transfer, not after it.