An Oracle to PostgreSQL migration 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 in an Oracle to PostgreSQL migration
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.
Oracle to PostgreSQL migration: details that need review
VARCHAR2 maps to varchar or text, DATE in Oracle includes a time part so it usually becomes timestamp, and CLOB and BLOB become text and bytea. Oracle treats an empty string as NULL, while PostgreSQL does not, which changes results for NOT NULL columns and comparisons. Packages, synonyms and PL/SQL have no direct PostgreSQL equivalent and are rewritten as schemas, views and PL/pgSQL functions. Sequences carry over, but triggers that fill ids from them are often replaced by identity columns. Plan time for these items, because a schema diff cannot decide them for you.
Oracle to PostgreSQL migration: 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.
Further reading: for more on Oracle to PostgreSQL migration, see the Oracle Database documentation and the PostgreSQL documentation.
