Oracle to PostgreSQL: the type mapping

The mapping

Most of a migration is mechanical. These are the correspondences that hold without further thought; the sections after this one cover the four that do not.

NUMBER without precision is the one that costs you

A bare NUMBER in Oracle holds up to 38 significant digits with any scale. The literal translation is numeric, and numeric in PostgreSQL is arbitrary-precision software arithmetic — correct, and several times slower than a machine integer.

The problem is that most columns declared NUMBER are not arbitrary at all. They are identifiers, counts and foreign keys that happen never to have been given a precision. Migrating them to numeric moves every join and every index onto the slow path, and the regression shows up as a general sluggishness nobody can point at.

Before migrating, look at what the column actually holds. A query like max(abs(col)) and max(scale) over the real data tells you in seconds whether a NUMBER is a bigint. Convert the ones that are; leave numeric for the ones where the scale is real — money, rates, measurements.

Oracle's DATE is not a date

Oracle's DATE carries year, month, day, hour, minute and second. PostgreSQL's date carries only the day. Mapping one to the other is the migration that silently drops the time from every row, and nothing errors — the data simply arrives at midnight.

The correct target is timestamp(0), or timestamptz if the column records an event rather than a wall-clock intention. Use date only where you have checked that the time component is always zero, which for a column named something like birth_date it usually is.

The reverse trap exists too. Oracle code that compares a DATE to TRUNC(SYSDATE) is comparing a timestamp to midnight, and the same comparison against a PostgreSQL timestamp needs a cast or a range, not an equality.

Empty string and NULL are the same thing in Oracle

Oracle stores an empty string as NULL. PostgreSQL treats them as different values. This is not a type mapping problem — the types line up fine — and it is the difference most likely to change what your application does after the migration.

Rows that arrive from Oracle with NULL may have been written as empty strings by code that then reads them back with NVL. In PostgreSQL that code now has to handle both, because new rows written by the same application will contain '' while migrated rows contain NULL.

• Decide which one means 'no value' in the new schema, and normalise the migrated data to it.
• Add a NOT NULL with a default, or a check constraint that rejects '', so the decision cannot drift.
• Search the application for NVL, COALESCE and IS NULL around text columns — those are the places that assumed Oracle's rule.

Identifier case folds the other way

Unquoted identifiers fold to upper case in Oracle and to lower case in PostgreSQL. A table created as Orders is ORDERS in Oracle and orders in PostgreSQL, and both are reachable unquoted — so far so good.

It breaks when the Oracle schema was created with quoted identifiers, because then the name really is ORDERS, and a migration tool that preserves it produces a PostgreSQL table that can only ever be reached as "ORDERS". Every query in the application then needs quoting, forever.

Fold the names to lower case during the migration rather than preserving them. It is a rename once, against quoting every identifier for the life of the schema.

Sequences and generated keys

Oracle's seq.NEXTVAL has a direct equivalent in nextval('seq'), so a literal translation works. It is usually worth going further and using an identity column, which attaches the sequence to the table and removes the chance of an insert that forgets it.

After loading data with explicit ids, the sequence is still at 1 and the next insert collides. Reset it with setval on the maximum existing id — a step that is easy to forget because nothing fails until the first insert after the migration.

Frequently asked

What does Oracle NUMBER map to in PostgreSQL?

numeric, literally — but that is usually the wrong answer. A NUMBER with no precision is almost always an integer in practice, and leaving it as numeric puts every join and index on arbitrary-precision arithmetic. Check the data and use integer or bigint where it fits.

Can I map Oracle DATE to PostgreSQL date?

Only if the time is always midnight. Oracle's DATE includes hours, minutes and seconds, so the usual target is timestamp(0), or timestamptz when the column records an event.

Why do empty strings behave differently after migrating?

Oracle stores an empty string as NULL; PostgreSQL keeps them distinct. Migrated rows arrive as NULL while new rows written by the same application contain '', so any code using NVL or IS NULL on text now has two cases to handle.

Should I keep the uppercase table names?

No. Oracle folds unquoted names to upper case, PostgreSQL to lower. Preserving the uppercase forces every identifier in every query to be quoted for the life of the schema. Fold to lower case during the migration.

What replaces ROWNUM?

LIMIT, with one difference that matters: ROWNUM is assigned before ORDER BY, so a query using both does not mean what it looks like. LIMIT applies after ordering, which is usually what the Oracle query was trying to express anyway.

Do I need to do anything about sequences after loading data?

Yes — reset them with setval to the maximum id you loaded. Nothing fails during the migration; the first insert afterwards collides on the primary key.