Leaving Oracle for PostgreSQL: A Field Guide

Oracle → PostgreSQL is rarely a technology decision first — it's usually a licensing decision that becomes a technology project. The good news: PostgreSQL is the closest open-source relative Oracle has, and the migration is well-trodden. The bad news: the differences that remain are subtle, and most of them fail silently rather than loudly.

NUMBER is bigger than you think

Oracle's NUMBER holds up to 38 significant digits. PostgreSQL's NUMERIC matches it — but the trap is in the middle layer: many drivers fetch NUMBER as a 64-bit float by default, which holds ~15 digits. Your database-to-database copy can lose precision in transit while both endpoints were perfectly capable of exactness. Fetch as decimals, always. And dimension the target honestly: a bare NUMBER with no declared precision should land as wide NUMERIC, not a guessed INTEGER.

Empty string IS NULL — until it isn't

Oracle treats '' as NULL. PostgreSQL treats them as different values. Code ported from Oracle often relies on that conflation (WHERE col IS NULL matching empty-string inserts). After migration the data is suddenly more precise than the application expects. Audit string columns where empty and NULL both appear, and decide which semantic the application actually wants.

Identifier casing flips

Oracle uppercases unquoted identifiers (employees is stored as EMPLOYEES); PostgreSQL lowercases them. Mechanical to convert, but any tooling that matches names case-sensitively — ORMs, report builders, hand-written introspection — needs the reconciliation handled consistently on both sides of the transfer.

Type map highlights

• VARCHAR2(n) → VARCHAR(n) — but Oracle measured bytes by default; multi-byte text needs character semantics or wider columns.
• DATE → TIMESTAMP — Oracle DATE carries a time component; mapping it to PG DATE drops it.
• CLOB/BLOB → TEXT/BYTEA — stream large values during transfer rather than loading them whole.
• RAW → BYTEA; TIMESTAMP WITH TIME ZONE → TIMESTAMPTZ (watch driver round-trips — some return wall-clock without offset).
• Sequences and GENERATED AS IDENTITY → PostgreSQL identity columns, reseeded to MAX(id) after load.

PL/SQL is the long tail

Tables and data migrate in hours; packages, triggers and procedures are where Oracle exits go to die. PL/SQL and PL/pgSQL look related but differ in package structure, exception handling, autonomous transactions, and a hundred built-ins. Treat procedural code as a porting project with human review, not a conversion step — migrate the data first, inventory the procedural objects, and convert them in priority order while both systems run.

Run both until the numbers match

The pattern that works: bulk-migrate, validate with counts and checksums, keep the target in sync with incremental updates, point read-only workloads at PostgreSQL first, and cut writes over last. Licensing pressure creates deadline anxiety; validation discipline is what keeps the deadline from creating data incidents.