Oracle-to-PostgreSQL migrations are usually approved for a straightforward reason: licensing cost. They usually run long for a less obvious one — the application layer assumed Oracle-specific behavior that nobody wrote down anywhere. Here's where that shows up.
PL/SQL doesn't translate 1:1
PostgreSQL's PL/pgSQL is close enough to PL/SQL to be dangerous — close enough that a stored procedure will often look portable and fail on a package variable, a cursor attribute, or an exception-handling pattern that has no direct PostgreSQL equivalent. Packages in particular don't exist in PostgreSQL; that logic has to be restructured into schemas and functions, which is a design decision, not a find-and-replace.
Sequence and identity behavior differences
Oracle sequences and PostgreSQL sequences behave similarly on the surface and differently at the edges — caching behavior, gap tolerance under rollback, and how identity columns interact with sequences all vary. Applications that assumed strictly gapless, monotonic IDs (more common than teams expect, usually in invoice or order numbering) need that assumption re-validated before cutover, not after.
Case sensitivity and quoting surprises
Oracle folds unquoted identifiers to uppercase. PostgreSQL folds them to lowercase. Anything that mixed quoted and unquoted identifiers — a common, mostly-harmless habit in Oracle — can produce PostgreSQL objects that don't match what application code expects to query. This is a small rule with an outsized number of "table doesn't exist" errors attached to it during first cutover attempts.
Connection pooling and driver behavior
Oracle client behavior around connection pooling, session state, and long-running transactions doesn't map directly onto PostgreSQL's connection model. Applications built assuming Oracle's connection semantics — particularly around session-level settings and implicit commits — need their pooling configuration (PgBouncer or equivalent) tuned deliberately, not left on defaults inherited from the old environment.
The part nobody budgets for: testing
Schema conversion and data migration are the visible 60% of the project. Regression-testing every report, batch job, and integration that touches the database — including the ones nobody remembers building — is the other 40%, and it's where realistic timelines usually get revised. Migrations that go well are the ones where testing time was budgeted from the start, not squeezed in before go-live.
This is exactly the work covered under our Database Engineering & Modernization practice — schema and PL/SQL conversion, sequence and identity validation, and a testing plan built before the cutover date is picked, not after.