For decades Oracle was the default choice for enterprise databases, and in many banks and enterprise systems it is still at the core. But new projects increasingly start on PostgreSQL, and older ones are moving to it. Let's look at how the databases actually differ and what moving from Oracle to PostgreSQL means in practice.

Oracle to PostgreSQL, a hypothetical 400-table database stage moved by manual work duration schema and typesora2pg2 days data, 800 GBdump6 hours of downtime PL/SQL, 900 proceduresby hand3 months application SQLby hand2 months this is where the real work hidesschema and data are a tool and a downtime window; the rest is by hand

Schema and types are ora2pg converts almost mechanically, and data is a question of the downtime window. PL/SQL and application queries, though, are rewritten by hand, line by line — and that is where the differing semantics surface: the empty string, dates, collation NULL. The bulk of the migration sits in the bottom two rows.

The main difference: cost and model

Both are mature relational databases with transactions, analytic functions, and rich SQL. The key difference is not in capabilities but in the model:

  • Oracle is a proprietary product licensed per core; the full feature set (partitioning, compression, advanced diagnostics) often comes as separate paid options. Its strengths are the ecosystem, support, and tooling for very large installations.
  • PostgreSQL is open and free; the same partitioning, window functions, JSON, and extensions are available out of the box. Eliminating license fees is precisely the most common motive for migration.

For most workloads PostgreSQL fully covers the needs; Oracle remains justified where you've already invested in its ecosystem or run into specific capabilities of very large scale.

Differences a developer notices

  • Stored logic. Oracle uses PL/SQL, PostgreSQL uses PL/pgSQL: the syntax is close but not identical; packages (PACKAGE) in PostgreSQL are replaced by schemas and sets of functions.
  • Auto-increment. In Oracle it's a SEQUENCE (and previously a sequence-plus-trigger combination); in PostgreSQL it's a SEQUENCE or, more simply, GENERATED AS IDENTITY / serial.
  • Empty string and NULL. Oracle treats the empty string '' as NULL — PostgreSQL distinguishes them. This is a classic source of subtle bugs during a migration.
  • Types. NUMBER → numeric/bigint, VARCHAR2 → varchar, DATE (with a time component) → timestamp. Dates are especially tricky: DATE in Oracle stores a time as well.
  • Hints and the plan. Oracle optimizer hints in queries (/*+ ... */) don't work in PostgreSQL — the plan is tuned through statistics, indexes, and query rewriting.

What a migration looks like

Moving is not only transferring data but also porting logic:

  1. Schema and data. The ora2pg tool converts the schema, types, and data and estimates the complexity of migrating the stored code.
  2. Stored procedures. PL/SQL is rewritten into PL/pgSQL or, more often the better choice, the logic is moved into the application — that makes it easier to test and version.
  3. Application changes. SQL with Oracle-specific syntax (ROWNUM, NVL, SYSDATE, CONNECT BY) is replaced with standard SQL (LIMIT, COALESCE, now(), recursive CTEs).
  4. Behavior verification. Places where the semantics differ are tested separately: empty strings, NULL ordering, numeric precision, and date handling.

Schema migrations in both databases are done in versioned steps — see Migrations in PostgreSQL.

In short

  • The capabilities of the databases are close; the main difference is that Oracle is proprietary and paid while PostgreSQL is open and free, which is what motivates most moves.
  • For a developer the differences are in stored logic (PL/SQL vs PL/pgSQL), auto-increment, types, and the handling of the empty string and NULL.
  • Migration: ora2pg for the schema and data, moving stored code into the application, and replacing Oracle-specific SQL with standard SQL.
  • The subtlest pitfalls are semantic: '' = NULL in Oracle, DATE with a time component, NULL ordering, numeric precision.
  • Oracle is justified where you've already invested deeply in its ecosystem or need its specific scale; for a new project the reasonable default is PostgreSQL.