Skip to content
IntoSQL home
Convert a file

Data types

PostgreSQL to Oracle data types

Every PostgreSQL data type that Oracle stores differently, what the converter writes for it, when, and the report line.

View as Markdown

Each row is a type from PostgreSQL that Oracle stores differently: what the Oracle script gets, why, and the line the report writes about it. The rows come from the converter's own rules, so this page matches what it does.

Convert in the browser: PostgreSQL to Oracle opens the converter set up for this pair. The PostgreSQL to Oracle migration guide covers dumping, loading and the rules for keys, indexes and names.

A report line is a fix when the converter changed something and lists it, or a problem when a value or object is worth a look.

Numbers

In PostgreSQL In Oracle Why Report line
numeric(p,s) past 38 digits; numeric without a precision NUMBER when its values fit 38 digits, else VARCHAR2 Oracle's NUMBER holds 38 digits at any scale. Wider numbers are kept as text so every digit stays exact. decimal_as_text (fix)
money NUMBER(19,2) Oracle has no money type. The amount is kept as a number, without the currency symbol the server's locale added. type_replaced (fix)
oid, xid, cid NUMBER(10) with a CHECK that it isn't negative NUMBER holds every value. None

Booleans and bits

In PostgreSQL In Oracle Why Report line
boolean NUMBER(1) with a CHECK of 0 and 1 Oracle 21c has no boolean column type; 1 and 0 keep true and false on every version. None
bit(n), bit varying NUMBER with the bits' value up to 63 bits; text digits beyond Bit strings are stored as the number their bits make. None

Text

In PostgreSQL In Oracle Why Report line
varchar(n), char(n) VARCHAR2(n CHAR), counting characters Lengths count characters as PostgreSQL's do. None
text VARCHAR2(4000 CHAR) when every value fits 4000 bytes (750 characters in a key), else CLOB A VARCHAR2 holds 4000 bytes by default and Oracle can't index a CLOB, so text keeps VARCHAR2 when its values fit and is a CLOB when they don't. text_as_lob (fix)
empty strings and empty bytea values NULL Oracle stores an empty string as NULL, so a value that was ' reads back as NULL. The report counts them per column. empty_string_null (problem)
text that isn't valid in its encoding the bad bytes as the replacement character The script is UTF-8 and loads with NLS_LANG=.AL32UTF8. invalid_utf8_replaced (problem)

Enums and sets

In PostgreSQL In Oracle Why Report line
enum types VARCHAR2 with a CHECK of its labels Text with a check keeps the same values. None
enum columns holding values outside their labels VARCHAR2 without a check Values the labels don't name would fail the check, so the check isn't created. enum_check_skipped (problem)

Dates and times

In PostgreSQL In Oracle Why Report line
interval VARCHAR2(100 CHAR) with PostgreSQL's text An interval mixes months with days and seconds, which Oracle keeps in two different types; the text keeps every part. type_replaced (fix)
timestamp TIMESTAMP A timestamp without time zone is a wall-clock reading, like Oracle's TIMESTAMP. date becomes DATE, which also holds a time of day that stays at midnight. None
timestamp with time zone TIMESTAMP WITH TIME ZONE, written in UTC Each value is written in UTC, the same instant whatever offset the dump wrote. None
time INTERVAL DAY TO SECOND from midnight Oracle has no time-of-day type; an interval from midnight holds every value, 24:00:00 included. None
infinity, -infinity, BC dates and years past 9999 NULL The script writes years 1 to 9999, and Oracle has no infinity. Those values become NULL and the report counts them. date_out_of_range (problem)

Binary

In PostgreSQL In Oracle Why Report line
bytea BLOB Bytes stay bytes. None

JSON, spatial and other types

In PostgreSQL In Oracle Why Report line
arrays (integer[], text[]...) CLOB with a JSON array Oracle has no arrays. A JSON array keeps every item and NULL, and JSON_TABLE reads it. type_replaced (fix)
ranges, geometric types, tsvector, xml, hstore, PostGIS, composite and extension types VARCHAR2 or CLOB with PostgreSQL's text Oracle has no equivalent type, so each value keeps the text PostgreSQL wrote. type_replaced (fix)
json, jsonb CLOB Oracle's JSON functions read JSON from text on every version, and a CLOB keeps values of any size. None
NaN and Infinity in numeric NULL Oracle's NUMBER has no NaN or Infinity, so those values become NULL and the report counts them. invalid_value_null (problem)
uuid, inet, cidr, macaddr VARCHAR2 Oracle has no such types; the text keeps every value. None
types IntoSQL doesn't know CLOB Text holds any value; change the type after loading. unknown_type (problem)

Counters and identities

In PostgreSQL In Oracle Why Report line
serial, identity GENERATED BY DEFAULT ON NULL AS IDENTITY, continuing after the highest id The copied ids load as they are, and the counter continues after the highest one or the sequence's next value, whichever is higher. None
a second serial or identity column in a table a plain integer Oracle counts one identity column per table; any other keeps its values without a counter. counter_dropped (problem)

Other pairs