Data Type Mapping
PostgreSQL has rich set of data types. Some of the important Data type conversion between Oracle and PostgreSQL is as follow.
| Oracle | PostgreSQL | Comment |
|---|---|---|
| VARCHAR2(n) | VARCHAR(n) | In Oracle ‘n’ is number of bytes whereas in PostgreSQL ‘n’ is number of characters |
| CHAR(n) | CHAR(n) | In Oracle ‘n’ is number of bytes whereas in PostgreSQL ‘n’ is number of characters |
| NUMBER(n,m) | NUMERIC(n,m) | NUMBER type can be converted to NUMERIC but if you use SMALLINT, INT and BIGINT then performance would be better. |
| NUMBER(4) | SMALLINT | |
| NUMBER(9) | INT | |
| NUMBER(18) | BIGINT | |
| NUMBER(n) | NUMERIC(n) | NUMERIC(n) ,If n>=19 |
| DATE | TIMESTAMP(0) | Both databases has DATE type but Oracle DATE type returns date and time whereas PostgreSQL DATE type return only date no time. |
| TIMESTAMP WITH LOCAL TIME ZONE | TIMESTAMPTZ | The PostgreSQL type Timestamptz(Timestamp with time zone) is different from the Oracle Timestamp with time zone. It is equivalent to Oracle’s Timestamp with local time zone, but this small difference can cause performance issue or application bug. |
| CLOB | TEXT | PostgreSQL TEXT type can store up to 1 GB of text. |
| BLOB RAW(n) | BYTEA(1 GB limit) Large object | In Oracle, BLOB datatype stores unstructured binary data in the database. BLOB type can store up to 128 terabytes of binary data. PostgreSQL BYTEA stores binary data but only upto 1 GB. If the data if above 1 GB then use Large object. |