Data Type Mapping: Oracle to PostgreSQL Complete Reference

The Reference Table You Wish Your Migration Tool Came With

Data type mapping is where every Oracle → PostgreSQL migration starts, and where the silent bugs hide if you get it wrong. A column declared NUMBER(10) mapped to INTEGERlooks correct on the schema diff — but if your data has a value that overflows int4, you find out at production cutover, not assessment.

This is the comprehensive reference: every common Oracle type, the PostgreSQL equivalent, and whether the mapping is clean (no semantic loss), note (something to be aware of), review (silent-data-loss risk if not checked), or manual (no direct equivalent — needs application change). Modern transpilers handle the clean and note cases automatically; the review and manual cases are what they surface for your eyes.

Numeric Types

Oracle's NUMBER is one type that maps to four different PostgreSQL types depending on precision and scale. Get this wrong and you either waste bytes or risk overflow.

The NUMBER-with-no-precision trap: Oracle lets you declare NUMBER with no precision or scale — effectively a free-form numeric. Mapping it blindly to NUMERIC in PG works but is slow and prevents the optimiser from making good choices. Whenever possible, tighten to NUMERIC(p, s) based on actual data ranges. A good migration tool surfaces every NUMBER-no-precision column in a risk report so you can audit them upfront.

String Types

The single biggest string-type gotcha is the BYTE vs CHAR semanticson VARCHAR2. Oracle defaults to byte semantics in some installs and character semantics in others — and the wrong choice on a UTF-8 multi-byte column causes silent truncation.

Check your NLS_LENGTH_SEMANTICS setting in Oracle before quoting timeline. If it's BYTE (the default in older installs) and your data has multi-byte UTF-8 strings, mapping VARCHAR2(50 BYTE) to VARCHAR(50) in PG can silently truncate non-ASCII rows. The safe rule: widen by 1.5× when migrating BYTE-semantics columns, OR audit your actual data first.

Date and Time

The single most common silent-bug in any Oracle → PostgreSQL migration: Oracle DATE includes time-of-day. PostgreSQL DATE does not. If you map Oracle DATE to PG DATE, every hour/minute/second is lost — and your application probably depends on those.

Modern transpilers default DATE → TIMESTAMP automatically. If you're writing your own conversion, lock this in by hand. It is genuinely the most common source of post-cutover bug reports.

LOBs and Binary Types

The BLOB-into-BYTEA performance question matters for large files. PostgreSQL stores BYTEA inline up to ~8 KB, then in a TOAST table beyond that — fine for most cases. But if you have multi-MB or GB binary columns, you'll get better performance moving them to object storage (S3, MinIO, GCS) and keeping only a URL/key column in the database. A migration is a good time to make that change.

XMLTYPE, Spatial, and Special Types

The Oracle-specific surface area. Some have direct PG equivalents (with caveats), some need application-level redesign.

XMLTYPE → JSONB is a semantic shift, not a translation. If your application queries XML data via XPath or XQuery, those queries need to be rewritten in JSON path syntax. PostgreSQL also has a proper XML type if you want to preserve the original format — but JSONB's indexing (GIN) is dramatically better, and most teams who started with XMLTYPE wish they'd started with JSONB anyway.

Things With No PostgreSQL Equivalent

A few Oracle types genuinely have no direct PG counterpart. Each requires an application-side decision before the migration starts:

• BFILE — read-only pointer to an OS file. PG has no equivalent; the path stays in the database but the file-system access must be reimplemented in the application layer.
• ANYDATA / ANYTYPE — polymorphic value storage. Refactor to a discriminator column ("type") + typed columns, or to a JSONB column with a tagged structure.
• VARRAY and nested table types — PG has array types (INT[], etc.) but the semantics differ on indexing and update. Often cleanest to migrate to a child table.
• ROWID dependence in app code — Oracle's ROWID is the physical address of a row. PG has CTID but it changes when a row is updated. Anywhere your application relies on ROWID identity across updates needs a real primary-key dependency instead.

Validating Type Mappings Post-Load

A clean type-mapping table on paper isn't enough. After bulk load, run two checks:

1. Per-column distribution check. Compare MIN, MAX, AVG, STDDEV on every numeric column. Identical statistics = data preserved. Any drift = precision loss somewhere.
2. Per-row checksum. Hash every row on both sides; compare. The Merkle approach (covered in our mathematical proof article) makes this fast enough to run on every migration.

Catch silent precision loss before users notice it.

The Honest Verdict

Type mapping is the layer that looks easy and hides the most bugs. About 90% of the columns in a typical enterprise schema map cleanly with no human decision needed. The remaining 10% — the unconstrained NUMBERs, the BYTE-semantics VARCHAR2s, the XMLTYPEs, the BFILEs — are where the time goes. A migration tool that catches all of them and surfaces them upfront for review is worth its weight; one that silently casts and hopes for the best is the same tool that gives migration projects their bad reputation.

The reference table above covers the 95% case. For the 5% that doesn't map cleanly — run a real assessment against your own schema. The answer is always more interesting than the textbook version.