Migrating from Oracle to PostgreSQL is often driven by licensing costs, and the schema is where it starts. Oracle's type system and object model differ enough that a manual translation is error-prone — here is what to watch at the schema level.
Number types
Oracle's single NUMBER type covers integers and decimals with optional precision and scale. In PostgreSQL these map to integer, bigint, or numeric(p,s) depending on how the column is used. A schema-aware diff picks the right target type instead of a blind copy.
Sequences and identity
Oracle traditionally uses explicit sequences plus triggers for auto-increment; newer versions have IDENTITY. PostgreSQL supports both sequences and GENERATED … AS IDENTITY, so the intent maps cleanly — but the trigger-based pattern needs review.
What needs manual attention
- PL/SQL packages, procedures, functions — no direct PostgreSQL equivalent; rewrite in PL/pgSQL.
- Materialized views — supported in both but with different refresh semantics.
- VARCHAR2 / CLOB / DATE — map to
varchar/text/timestamp.
Compare, then migrate
Run a cross-dialect schema diff from Oracle to your PostgreSQL target to generate the structural DDL and flag procedural objects for manual work. FoxSchema supports both. Download it.
