Conversion tools map every Oracle column to a PostgreSQL type in minutes. The default they choose is a starting point. This guide lists the mappings that are safe, the ones that need a decision, and the checks that prove the result before cutover.

NUMBER is the first decision

Oracle uses NUMBER for almost everything numeric. PostgreSQL has distinct types, and the choice affects storage, join speed and application behavior.

  • NUMBER(p,s) with a scale maps to numeric(p,s).
  • NUMBER(p) used for keys and counters maps to integer or bigint, chosen from the precision. Leaving keys as numeric works and is slower in joins and indexes.
  • NUMBER with no precision needs profiling. Look at the data: integers only, or decimals. Map to bigint or numeric accordingly.
  • NUMBER(1) used as a flag can become boolean. That changes application code and is a decision, not a default.
  • BINARY_FLOAT maps to real. BINARY_DOUBLE and FLOAT map to double precision.

DATE is not a date

  • Oracle DATE holds a time to the second. It maps to timestamp(0). Mapping it to PostgreSQL date silently drops the time.
  • TIMESTAMP maps to timestamp. Oracle allows nine fractional digits, PostgreSQL six.
  • TIMESTAMP WITH TIME ZONE maps to timestamptz, which stores the instant in UTC and does not keep the original zone. If the zone is business data, store it in a second column.
  • TIMESTAMP WITH LOCAL TIME ZONE also maps to timestamptz. Check the session time zone settings on both sides.
  • INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND map to interval.

Strings, and the empty string

  • VARCHAR2(n) and NVARCHAR2(n) map to varchar(n). Check whether the Oracle column was declared in bytes or characters. PostgreSQL counts characters, so a byte-declared column can accept longer values after migration.
  • CHAR(n) maps to char(n).
  • CLOB, NCLOB and LONG map to text.
  • Oracle treats an empty string as NULL. PostgreSQL does not. After migration, '' and NULL are different values. Predicates, unique constraints, NOT NULL columns and application checks that relied on the Oracle rule will behave differently.
  • Concatenation follows from that. In Oracle, 'a' || NULL is 'a'. In PostgreSQL it is NULL. Use concat() or COALESCE where the Oracle result is required.

Binary and special types

  • BLOB, RAW and LONG RAW map to bytea, which holds up to 1 GB per value. Very large objects need a storage decision outside the row.
  • BFILE has no equivalent. Store a path or move the files to object storage.
  • ROWID and UROWID have no stable equivalent. Code that stores or joins on them needs a real key.
  • XMLTYPE maps to xml. SDO_GEOMETRY maps to PostGIS types.
  • Object types, nested tables and VARRAY need a design: composite types, arrays, or child tables.

What breaks that is not a type

  • Identifier case. Oracle folds unquoted names to upper case, PostgreSQL to lower case. Quoted mixed-case names from generated DDL stay quoted forever unless renamed once.
  • Sequences. seq.NEXTVAL becomes nextval('seq'). Set every sequence past the migrated maximum before the first insert.
  • Synonyms. PostgreSQL has none. Use search_path or views.
  • Global temporary tables. PostgreSQL temporary tables are created per session. Code that assumes a permanent definition has to create it.
  • Materialized views. There is no refresh on commit and no fast refresh. Refresh becomes a scheduled job or a redesign.
  • Index-organized tables and bitmap indexes. Neither exists as such. Plans and index choices are redone for PostgreSQL.

What to test before cutover

-- Run on both sides, per table, and compare.
-- The empty-string count is the one that surprises people.
SELECT count(*)                                  AS row_count,
       count(customer_ref)                       AS not_null_refs,
       sum(CASE WHEN customer_ref = '' THEN 1 ELSE 0 END) AS empty_refs,
       min(created_at), max(created_at),
       sum(amount)                               AS amount_total
FROM   orders;
  • Row counts and aggregate totals per table, on a repeatable schedule.
  • NULL counts per text column on both sides, to expose the empty-string difference.
  • Minimum and maximum of every date column, with the time portion, to expose a DATE mapped to date.
  • Maximum value per numeric key against the chosen integer type.

Record each mapping decision with its reason. The team that operates the database after cutover will need to know why a flag became boolean or why a zone lives in its own column.

Where this leads

Datrick operates Oracle and PostgreSQL in production and delivers Oracle to PostgreSQL migration under a written scope, with named engineers accountable for assessment, conversion, validation, cutover and the operation that follows. The service page describes the delivery model: Oracle to PostgreSQL migration. For a first read of risk and effort, see the migration risk assessment.

Related: Oracle to PostgreSQL migration · migration risk assessment · Oracle DBA services · PostgreSQL DBA services · SQL Server data type mapping