How to Migrate Oracle to PostgreSQL

Most teams move from Oracle to PostgreSQL for one reason: cost.
Oracle licensing is expensive and aggressively audited;
PostgreSQL is open source, free to run, and has closed most of the feature gap that used to justify the bill.

But the move is not a lift-and-shift. Tables and data migrate cleanly with the right tool - it's the code (PL/SQL packages, triggers, and the Oracle-specific SQL scattered through your application) that takes the real work, and where most projects underestimate the effort. Here's the whole path at a glance, then the detail - with a copy-paste before/after block for every Oracle-ism.

Migrating from MySQL instead? See How to Migrate MySQL to PostgreSQL.

Quick answer: the safest Oracle to PostgreSQL migration path

1. Assess schema, data size, and PL/SQL volume.
2. Convert table structures and data types.
3. Bulk-load data into PostgreSQL.
4. Recreate indexes, constraints, and sequences.
5. Rewrite and test Oracle SQL and PL/SQL (AI/tools draft it; you verify the logic).
6. Validate counts, reports, and application behavior.
7. Cut over, with DBSync if downtime must be short.

Steps 2-4 (schema, data, indexes) are what a converter automates end to end.
Step 5 - rewriting SQL and PL/SQL - is human work no matter which tool you use.

Knowing that split up front is the difference between a migration that lands on schedule and one that doesn't.

Step 1 - Assess the Oracle database first

Before moving anything, take inventory.
A solid assessment tells you two things:
- what actually needs to move, and
- how big the real effort is - which on Oracle is driven by procedural code, not data volume (PL/SQL remediation routinely eats more than half the total on a large estate).

Pull a quick inventory from the data dictionary:

-- Object counts by type
SELECT object_type, COUNT(*)
FROM   user_objects
GROUP  BY object_type
ORDER  BY object_type;

-- Lines of PL/SQL to port, biggest first
SELECT name, type, MAX(line) AS lines
FROM   user_source
GROUP  BY name, type
ORDER  BY lines DESC;

Start by deciding what not to migrate. History and audit tables, staging and temp tables, and old logging tables often don't need to move - archive them on Oracle or export to cold storage instead of carrying dead weight into PostgreSQL. Trimming scope here shrinks the data load, the validation surface, and the go-live window.

Then size the procedural code - the amount of PL/SQL still sets your timeline, but the bottleneck has shifted. AI-assisted translation can speed up the first draft of simple routines. The real work is verifying that the business logic survived the move and behaves correctly on PostgreSQL: transaction boundaries, exception handling, date math, dynamic SQL, package state, and edge cases.

• A few hundred lines of simple procedures - quick to draft, then a focused test pass.
• Tens of thousands of lines across packages, triggers, dynamic SQL, and autonomous transactions - the generated draft is only the start; validation is the schedule.

So don't estimate PL/SQL by typing time - estimate it by review and test time.

Step 2 - Move the schema and map the data types

Recreating tables is mechanical except for the type system, where Oracle and PostgreSQL disagree in ways that bite later if you get them wrong.

Two mappings cause most of the silent data corruption, so get them right first.

DATE → timestamp, never date. In Oracle, DATE is really a timestamp. Map it to date and you drop the time-of-day on every row - nothing errors, so you find out in production.

Unconstrained NUMBER - choose exact vs. fast on purpose. In Oracle it's arbitrary-precision and exact. In PostgreSQL you pick the target:

• numeric - exact, preserves every digit. The safe default for money, identifiers, and measured values.
• double precision - faster, but lossy: it quietly rounds large or high-precision values. Use only when the column tolerates rounding.
• bigint - only for whole-number keys.

Watch out: some tools (including ora2pg) default NUMBER to double precision. A tool with reviewable mapping like DBConvert lets you keep it numeric.

A converter handles this part in full. In DBConvert, the Oracle tables, columns, data, indexes, primary keys, and relationships transfer automatically, and the type mapping is shown for review before any rows move - so you can catch the two traps above and adjust per-column if needed.

Mapping can be set globally for the whole database or overridden field by field:

Step 3 - Move the data

With the schema in place, load the rows. Two things make this faster and safer:

• Add foreign keys, constraints, and most indexes after the load, not before. Enforcing constraints during a bulk insert slows it down and can fail on rows that arrive out of order.
• Use bulk copy, not row-by-row INSERT. PostgreSQL's COPY is dramatically faster for large tables.

One correctness trap to plan for here - it's the most subtle difference between the two engines:

Watch out: empty string is not NULL in PostgreSQL.Oracle: an empty string '' is NULL.PostgreSQL: '' is a real, distinct empty string.

So any col IS NULL check that caught empty Oracle values behaves differently after the move. Decide per column whether '' should become NULL, and normalize it during the load.

-- Oracle: '' was indistinguishable from NULL
-- PostgreSQL: make the intent explicit during/after load
UPDATE my_table SET name = NULL WHERE name = '';

Prefer not to hand-roll the load? DBConvert moves the data in the same job that built the schema - no COPY scripts to write and maintain:

• The type mapping you confirmed in Step 2 is applied as the rows transfer.
• The job is saved and repeatable - one dry run to validate, then a final production run against the same source, nothing re-scripted between.
• The free trial has no speed limit, so measure real throughput on a partial run before you commit. (It tags rows with a TRIAL TEXT marker - for measuring, not seeding production.)

On a large load, migration speed is rarely the tool itself - it's network bandwidth, source/target server I/O, and whether tables can be moved in parallel. Tune those before blaming the migration.

Step 4 - Translate the Oracle SQL dialect

This is the part the migration tools leave to you - and the part most reference pages list without real examples. Here are the Oracle-isms you'll hit in queries and views, each with its PostgreSQL rewrite:

ROWNUM → LIMIT / ROW_NUMBER()

Oracle's ROWNUM has no direct equivalent. For a simple cap, use LIMIT. For top-N-per-group or ranking, use ROW_NUMBER().

-- Oracle
SELECT * FROM orders WHERE ROWNUM <= 10;

-- PostgreSQL
SELECT * FROM orders LIMIT 10;

Oracle's classic "ROWNUM pagination" pattern maps to LIMIT … OFFSET:

-- Oracle: rows 11–20
SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM (
    SELECT * FROM orders ORDER BY created_at
  ) t WHERE ROWNUM <= 20
) WHERE rn > 10;

-- PostgreSQL
SELECT * FROM orders ORDER BY created_at LIMIT 10 OFFSET 10;

CONNECT BY → WITH RECURSIVE

Oracle's hierarchical CONNECT BY becomes a recursive common table expression in PostgreSQL.

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

-- PostgreSQL
WITH RECURSIVE org AS (
  SELECT employee_id, manager_id, last_name, 1 AS level
  FROM   employees
  WHERE  manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.last_name, o.level + 1
  FROM   employees e
  JOIN   org o ON e.manager_id = o.employee_id
)
SELECT employee_id, manager_id, last_name, level FROM org;

LEVEL becomes a counter you carry in the recursive term. PRIOR defines the join back to the parent row.

Sequences: seq.nextval → nextval('seq')

-- Oracle
INSERT INTO orders (id, total) VALUES (order_seq.NEXTVAL, 99.0);
SELECT order_seq.CURRVAL FROM dual;

-- PostgreSQL
INSERT INTO orders (id, total) VALUES (nextval('order_seq'), 99.0);
SELECT currval('order_seq');

For new tables, prefer an identity column instead of a standalone sequence:

CREATE TABLE orders (
  id    bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  total numeric(12,2)
);

After a bulk load, reset each sequence. The data arrives with its existing keys, but the sequence still starts at 1 - so the first application insert collides with an existing row (duplicate key value violates unique constraint). Set the sequence one past the current maximum:

-- nextval will now return MAX(id) + 1
SELECT setval('order_seq', (SELECT MAX(id) FROM orders));

For an IDENTITY column, restart its owned sequence instead: ALTER TABLE orders ALTER COLUMN id RESTART WITH <max+1>;.

NVL → COALESCE

-- Oracle
SELECT NVL(phone, 'n/a') FROM customers;

-- PostgreSQL
SELECT COALESCE(phone, 'n/a') FROM customers;

COALESCE takes any number of arguments and returns the first non-NULL - it's a superset of NVL. (Oracle's NVL2(a,b,c) → CASE WHEN a IS NOT NULL THEN b ELSE c END.)

DECODE → CASE

-- Oracle
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM accounts;

-- PostgreSQL
SELECT CASE status
         WHEN 'A' THEN 'Active'
         WHEN 'I' THEN 'Inactive'
         ELSE 'Unknown'
       END
FROM accounts;

SYSDATE → CURRENT_TIMESTAMP

-- Oracle
SELECT SYSDATE, SYSTIMESTAMP FROM dual;

-- PostgreSQL
SELECT CURRENT_TIMESTAMP, clock_timestamp();

Related: TRUNC(date) → date_trunc('day', ts); ADD_MONTHS(d, n) → d + (n || ' months')::interval; MONTHS_BETWEEN has no exact equivalent - compute with age() / extract().

DUAL → drop the FROM clause

PostgreSQL doesn't need a dummy table for constant selects.

-- Oracle
SELECT 1 FROM dual;

-- PostgreSQL
SELECT 1;

If you have many FROM dual queries you can't edit all at once, create a one-row dual view as a compatibility shim - but removing it is cleaner.

MERGE → INSERT … ON CONFLICT

Oracle's MERGE ("upsert") maps to PostgreSQL's INSERT … ON CONFLICT.

-- Oracle
MERGE INTO inventory t
USING staging s ON (t.sku = s.sku)
WHEN MATCHED THEN UPDATE SET t.qty = s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);

-- PostgreSQL
INSERT INTO inventory (sku, qty)
SELECT sku, qty FROM staging
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

(Modern PostgreSQL has a native MERGE statement, but INSERT … ON CONFLICT is still the common upsert form when a unique key defines the conflict.)

Old-style outer joins: (+) → LEFT JOIN

-- Oracle (pre-ANSI)
SELECT * FROM a, b WHERE a.id = b.id(+);

-- PostgreSQL
SELECT * FROM a LEFT JOIN b ON a.id = b.id;

Other quick swaps: MINUS → EXCEPT; || concatenation works in both (but remember NULL handling differs); INSTR → strpos/position (argument order differs); SUBSTR works in both.

Quoted identifiers and case sensitivity

Oracle folds unquoted identifiers to UPPERCASE; PostgreSQL folds them to lowercase. As long as nobody quotes them, both resolve case-insensitively and you never notice. The trap is double-quoted identifiers:

-- Unquoted works in both engines:
SELECT * FROM emp;

-- But once an identifier is quoted, PostgreSQL makes it case-sensitive -
-- so a table created as "EMP" must be quoted forever:
SELECT * FROM "EMP";   -- works
SELECT * FROM emp;     -- ERROR: relation "emp" does not exist

Some tools quote every identifier "to be safe," which locks you into "EMP"."COL" in every query and view from then on. Prefer letting names fold to lowercase (unquoted). Quoting is only worth it for a genuine reserved word (user, order, desc, group...) - and renaming the column is usually cleaner than quoting it everywhere.

Step 5 - Port PL/SQL to PL/pgSQL

This is the hard stage, and it's worth being blunt: no automated tool should be treated as producing production-ready PL/pgSQL for non-trivial PL/SQL. Converters scaffold a translation you then fix by hand; AI assistants help with boilerplate; everything non-trivial gets human review.

On large estates this stage can exceed 60% of total migration effort - and most of that is now verification and testing, not typing. AI can help draft a PL/SQL → PL/pgSQL translation, but treat the output as code-review material, not production-ready migration output. Plan and budget for it as the main event, not a cleanup task.

What actually changes:

• Packages don't exist in PostgreSQL. A package becomes a schema (namespace) holding the individual functions; package state/variables need rethinking (often a per-session GUC or temp table).
• Procedures vs functions. Oracle procedures map to PostgreSQL PROCEDURE (PG 11+) or functions returning void.
• Exceptions. RAISE_APPLICATION_ERROR(-20001, 'msg') → RAISE EXCEPTION 'msg'; named exceptions and EXCEPTION WHEN OTHERS exist but SQLCODE/SQLERRM semantics differ.
• Dynamic SQL. EXECUTE IMMEDIATE → EXECUTE.
• %TYPE and %ROWTYPE carry over almost unchanged - a small mercy.
• Autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION) have no direct equivalent; use dblink or a background worker pattern.
• Built-in packages. DBMS_OUTPUT.PUT_LINE → RAISE NOTICE; UTL_FILE, DBMS_SCHEDULER, etc. need PostgreSQL replacements (e.g. pg_cron).

A minimal shape comparison:

-- Oracle
CREATE OR REPLACE FUNCTION get_name(p_id NUMBER) RETURN VARCHAR2 IS
  v_name customers.name%TYPE;
BEGIN
  SELECT name INTO v_name FROM customers WHERE id = p_id;
  RETURN v_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RETURN NULL;
END;

-- PostgreSQL
CREATE OR REPLACE FUNCTION get_name(p_id integer)
RETURNS varchar AS $$
DECLARE
  v_name customers.name%TYPE;
BEGIN
  SELECT name INTO STRICT v_name FROM customers WHERE id = p_id;
  RETURN v_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Note the STRICT keyword: in PL/pgSQL, SELECT INTO STRICT is what raises NO_DATA_FOUND / TOO_MANY_ROWS the way Oracle does implicitly.

Where DBConvert fits: it moves the schema and data both ways, Oracle ↔ PostgreSQL - tables, columns, data, indexes, keys, relationships - but it does not translate PL/SQL. Keep views, packages, triggers, and PL/SQL as a separate, manual rewrite.

Step 6 - Validate and cut over

Before you switch the application:

• Compare row counts table by table (SELECT COUNT(*) on both sides), and spot-check aggregates (SUM/MIN/MAX) on key columns.
• Re-run representative queries and reports against PostgreSQL and diff the results, not just the row counts.
• Verify migrated objects match - tables, indexes, constraints, keys, and relationships should be present on PostgreSQL; rewritten views should be checked as a separate SQL-dialect task.

For the switch itself, you have two shapes:

• Big-bang: freeze writes on Oracle, run the final data load, switch the app. Simplest; needs a maintenance window sized to your data volume (run a timed dry-run first).
• Near-zero downtime: keep Oracle live, do the bulk load, then continuously sync changes to PostgreSQL until you flip the application over. This is where ongoing replication earns its keep - DBConvert offers Oracle ↔ PostgreSQL sync for exactly this window, so the target stays current while you test, and the switch is just a connection-string change. See the Oracle → PostgreSQL converter and sync tool for the data-and-sync side of this.

After go-live: tune PostgreSQL

PostgreSQL plans queries from table statistics, and a freshly loaded database has none - so the first reports can feel slow until you settle it in:

• Run ANALYZE (or VACUUM ANALYZE) so the planner has current statistics.
• Check the indexes are all in place. If you deferred them during a manual bulk load, create them now; a converter like DBConvert brings them over with the job. Either way, confirm Oracle index types have PostgreSQL equivalents - function-based indexes become expression indexes, but bitmap and reverse-key indexes have no direct equivalent.
• Check slow queries with EXPLAIN (ANALYZE, BUFFERS). Oracle optimizer hints don't carry over; the PostgreSQL planner makes its own choices.
• Install the extensions your schema needs - PostGIS for spatial, pg_trgm for text search, or orafce for Oracle-compatible functions that shorten the SQL rewrite.

A note on compatibility shims: extensions like orafce (Oracle-style functions) or the oracle.date type can ease a transition, but lean on them deliberately - they're a bridge to buy time, not the destination. Code written against native PostgreSQL ages better than code that depends on an Oracle-emulation layer.

The DBConvert fast path (schema + data)

If you want the schema-and-data part done without scripting, here's the shape of it with DBConvert:

1. Connect to the Oracle source and the PostgreSQL target.
2. Review the type mapping - the proposed Oracle → PostgreSQL types are shown before anything runs; override per column where needed.
3. Run the job - tables, columns, data, indexes, keys, and relationships transfer in one pass. Save it as a repeatable job for a dry-run-then-production workflow.
4. Add sync (optional) for a near-zero-downtime switch.

Oracle to PostgreSQL migration tools compared

There's no single right tool - it depends on where your migration is hardest and where PostgreSQL is hosted.

All three leave PL/SQL as manual work - that part is the migration itself, not the tooling.

FAQ

Can I migrate Oracle to PostgreSQL for free? Partly. Free tools like ora2pg cover assessment and a first-pass schema-and-data export, but expect a Perl/Oracle-client setup and manual PL/SQL fixing - the "free" cost is your time. A converter like DBConvert does the schema-and-data part with reviewable mapping and a repeatable job in a fraction of that effort; either way, no tool removes the PL/SQL porting work.

How long does an Oracle → PostgreSQL migration take? Schema and data are quick - hours to a day or two depending on volume, and faster still with a converter that runs it as a saved job. The timeline is driven by procedural code: size the PL/SQL first (see Step 1) before you commit to a date.

Does DBConvert convert PL/SQL or Oracle views? No. DBConvert handles the mechanical schema-and-data migration: tables, columns, data, indexes, keys, and relationships. Oracle views, PL/SQL packages, procedures, functions, triggers, jobs, and application SQL should be treated as a manual rewrite and validation task - see Step 5.

Can I migrate PostgreSQL back to Oracle? Yes - the schema-and-data path works both directions. The same dialect differences apply in reverse (e.g. LIMIT → ROWNUM, COALESCE → NVL). DBConvert supports PostgreSQL → Oracle as well.

Conclusion

An Oracle-to-PostgreSQL migration is two projects wearing one name. The schema-and-data move is mechanical and well-automated - a converter with reviewable type mapping and a repeatable load gets you there fast. The SQL dialect and PL/SQL are where the real work is, and where being honest up front - size the procedural code, budget the human-days - keeps the project on schedule.

The playbook: move the structure and data with a tool, translate the dialect with the patterns above, port the procedural code by hand, validate, then cut over - optionally with a sync window so downtime is measured in minutes.