Published: 2026-06-14

Migrate Oracle to PostgreSQL

Jam SQL Studio migrates Oracle tables to PostgreSQL — schema and data — from the Object Explorer. Right-click a table or database, point it at a PostgreSQL connection, and Jam translates the types, copies the rows, and verifies the counts. The headline translations: VARCHAR2 → varchar, NUMBER → numeric/integer, DATE → timestamp, and CLOB → text.

The workflow — connect both, right-click Migrate Table to Connection…, Preview, Migrate — is shared across pairs; see the cross-engine workflow. This page covers what's specific to Oracle → PostgreSQL, including the two Oracle quirks that catch people: the one numeric type and the time-carrying DATE.

Oracle → PostgreSQL type mapping

OraclePostgreSQLNote
VARCHAR2(n) / NVARCHAR2(n)varchar(n)
CHAR(n) / NCHAR(n)character(n)
NUMBER(p,0) / INTEGERsmallint / integer / bigintInteger width chosen by precision
NUMBER(p,s) (s>0)numeric(p,s)Exact
NUMBER (no p/s)numericArbitrary precision, faithful
FLOATdouble precisionOracle FLOAT is binary float precision
BINARY_FLOATreal
BINARY_DOUBLEdouble precision
DATEtimestampGotcha: Oracle DATE carries time-of-day — not PostgreSQL date
TIMESTAMP(n)timestamp(n)
TIMESTAMP WITH TIME ZONEtimestamp with time zone
TIMESTAMP WITH LOCAL TIME ZONEtimestampTz-less (no offset stored)
CLOB / NCLOB / LONGtext
BLOB / LONG RAWbytea
RAW(n)bytea

Oracle-specific gotchas

  • One numeric type. Oracle's NUMBER covers everything from a boolean flag to a 38-digit decimal. Jam infers the right PostgreSQL type from precision and scale: zero-scale is an integer (sized by precision), positive-scale is numeric. Review columns declared as bare NUMBER with no precision — they map to unconstrained numeric, which is correct but you may want a tighter type.
  • DATE has a time. The single biggest Oracle surprise: DATE stores year-month-day and hour-minute-second. Mapping to PostgreSQL date would lose the time, so Jam maps to timestamp. If a column is genuinely date-only, narrow it to date after migrating.
  • Empty string is NULL. Oracle treats '' as NULL; PostgreSQL treats it as a real empty string. The migrated data reflects what Oracle stored (NULLs), but application logic that relied on this equivalence needs review.
  • Sequences and identity. Oracle's traditional sequence-plus-trigger auto-increment doesn't carry as a single object; an Oracle 12c+ GENERATED AS IDENTITY column maps to the PostgreSQL equivalent. Recreate sequence-driven keys on the PostgreSQL side as GENERATED AS IDENTITY or an explicit sequence.
  • Case folding. Oracle upper-cases unquoted identifiers; PostgreSQL lower-cases them. A table Oracle stored as EMPLOYEES becomes employees in PostgreSQL unless quoted.

What stays manual

Tables, types, constraints, indexes, and data migrate automatically. PL/SQL does not: packages, procedures, functions, and triggers are Oracle-dialect code with no 1:1 PL/pgSQL translation (Oracle packages have no PostgreSQL equivalent at all and are usually refactored into schemas of functions). After migrating, run a cross-engine Schema Compare for the by-name inventory of what's missing, then port each object by hand. See Finish a PostgreSQL schema after migration for the checklist and the PL/pgSQL reference for the target dialect.

Frequently asked questions

How does Oracle NUMBER map to PostgreSQL?

Oracle has a single numeric type, NUMBER(p,s). A zero-scale NUMBER (or INTEGER, which Oracle stores as NUMBER(*,0)) is an integer and maps to PostgreSQL smallint, integer, or bigint by its precision. A NUMBER with a scale greater than zero, or a plain unconstrained NUMBER, maps to PostgreSQL numeric, which is arbitrary precision and faithful.

Why does Oracle DATE become timestamp in PostgreSQL, not date?

Because Oracle's DATE type carries a time-of-day component (down to the second), unlike the SQL standard DATE. Mapping it to a PostgreSQL date would silently drop the time. Jam maps Oracle DATE to PostgreSQL timestamp to preserve the full value; use PostgreSQL date explicitly only if you know the time is always midnight.

Does migrating Oracle to PostgreSQL move PL/SQL packages and procedures?

No. Tables, types, constraints, indexes, and data migrate automatically, but PL/SQL packages, procedures, functions, and triggers are written in Oracle's PL/SQL and do not translate 1:1 to PostgreSQL's PL/pgSQL. Jam gives you a by-name inventory of the procedural objects to port by hand rather than emitting a machine translation.