Automatic Data Type Inference: AI vs Manual Mapping

The Mapping Table Is the Easy Part

Every Oracle-to-PostgreSQL guide includes a type mapping table, and they all broadly agree. VARCHAR2 becomes VARCHAR, CLOB becomes TEXT, BLOB becomes BYTEA. If that were the whole job, nobody would need tooling for it.

The hard part is NUMBER — a single Oracle type standing in for everything from a boolean flag to a 38-digit decimal — and a handful of traps where the obvious mapping is quietly wrong.

The NUMBER Problem

NUMBER(p,0) could be an id, a flag, a count, or a year. Mapping every one to NUMERIC is technically correct and a performance mistake: PostgreSQL NUMERIC is arbitrary-precision software arithmetic, while INTEGER and BIGINT are native machine types. On a high-volume key column that difference is measurable, and it compounds through every index and join.

NUMBER(1)      -> SMALLINT   (or BOOLEAN if the data says so)
NUMBER(5,0)    -> INTEGER
NUMBER(10,0)   -> BIGINT
NUMBER(p,s)    -> NUMERIC(p,s)   -- money, keep exact
NUMBER         -> NUMERIC        -- unscaled, no safe narrowing

Inference: Declaration Plus Data

Precision alone doesn’t settle it, because Oracle schemas are full of columns declared far wider than anything they ever held. So inference looks at both the declaration and the actual distribution: observed minimum and maximum, whether any value has a fractional part, distinct-value count, and null ratio.

A NUMBER(1) holding only 0 and 1 across ten million rows is a boolean, and saying so is more useful than a faithful SMALLINT. A NUMBER(12,2) is money — keep it exact, never map it to a float. The declaration says what is permitted; the data says what is true.

This is the part that has to stay a proposal rather than a decision. Narrowing a type based on observed data is an inference about the future, and only the person who knows the application can confirm it. So inference proposes with its evidence attached, and a human accepts.

Four Mappings That Look Right and Aren’t

• DATE. Oracle DATE carries a time component. Mapping it to PostgreSQL DATE silently truncates every timestamp. It is TIMESTAMP.
• VARCHAR2 length. VARCHAR2(50) may mean 50 bytes, not characters. Migrating multi-byte data from a byte-semantics column overflows unless you widen it.
• CHAR. Blank-padded in both engines, but comparison semantics differ. Most CHAR columns want to be VARCHAR — check what the application expects first.
• Empty string. Not a type mapping at all, but it rides along with one: Oracle stores ’’ as NULL and PostgreSQL doesn’t. Decide per column and assert it in validation.

AI vs Manual

The framing is a little false. Nothing here needs a language model — it needs rules over statistics, which is what we use, because a mapping that is right 98% of the time is a mapping you cannot trust anywhere. What automation genuinely buys is scale and consistency: proposing a mapping for eleven thousand columns with the evidence attached, so a human reviews the interesting hundred instead of typing all eleven thousand.