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 tonumeric(p,s).NUMBER(p)used for keys and counters maps tointegerorbigint, chosen from the precision. Leaving keys asnumericworks and is slower in joins and indexes.NUMBERwith no precision needs profiling. Look at the data: integers only, or decimals. Map tobigintornumericaccordingly.NUMBER(1)used as a flag can becomeboolean. That changes application code and is a decision, not a default.BINARY_FLOATmaps toreal.BINARY_DOUBLEandFLOATmap todouble precision.
DATE is not a date
- Oracle
DATEholds a time to the second. It maps totimestamp(0). Mapping it to PostgreSQLdatesilently drops the time. TIMESTAMPmaps totimestamp. Oracle allows nine fractional digits, PostgreSQL six.TIMESTAMP WITH TIME ZONEmaps totimestamptz, 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 ZONEalso maps totimestamptz. Check the session time zone settings on both sides.INTERVAL YEAR TO MONTHandINTERVAL DAY TO SECONDmap tointerval.
Strings, and the empty string
VARCHAR2(n)andNVARCHAR2(n)map tovarchar(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 tochar(n).CLOB,NCLOBandLONGmap totext.- Oracle treats an empty string as NULL. PostgreSQL does not. After migration,
''and NULL are different values. Predicates, unique constraints,NOT NULLcolumns and application checks that relied on the Oracle rule will behave differently. - Concatenation follows from that. In Oracle,
'a' || NULLis'a'. In PostgreSQL it is NULL. Useconcat()orCOALESCEwhere the Oracle result is required.
Binary and special types
BLOB,RAWandLONG RAWmap tobytea, which holds up to 1 GB per value. Very large objects need a storage decision outside the row.BFILEhas no equivalent. Store a path or move the files to object storage.ROWIDandUROWIDhave no stable equivalent. Code that stores or joins on them needs a real key.XMLTYPEmaps toxml.SDO_GEOMETRYmaps to PostGIS types.- Object types, nested tables and
VARRAYneed 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.NEXTVALbecomesnextval('seq'). Set every sequence past the migrated maximum before the first insert. - Synonyms. PostgreSQL has none. Use
search_pathor 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
DATEmapped todate. - 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
