There are many incompatibilities in Oracle and PostgreSQL. The most common difference many people might notice is – PostgreSQL has rich set of data types. As of PostgreSQL version 10 an empty PostgreSQL database will expose the staggering number of 92 data types. Of course not all of them are useful and many of them are purely for internal purposes.
Before you begin with code migration from Oracle, it is utmost important to understand the data types in PostgreSQL. Some of the important Data type conversion between Oracle and PostgreSQL is as follow.
Data type Mapping
| Oracle | PostgreSQL |
|---|---|
| INT, INTEGER | INT, INTEGER |
| NUMBER(p); p < 5 | SMALLINT |
| NUMBER(p); p < 9 | INTEGER |
| NUMBER(p); p < 19 | BIGINT |
| NUMBER(p); p >= 19 | NUMERIC |
| NUMBER | Depends on what type of data is stored. Decide wisely between SMALLINT / INTEGER / BIGINT / DOUBLE PRECISION / NUMERIC |
| NUMBER(p, s) | DECIMAL(p,s) or NUMERIC(p, s) – Choose only in case of decimal data. Choose INTEGER/BIGINT instead to stored integer data values. |
| VARCHAR2(n) | VARCHAR(n) or CHARACTER VARYING(n) |
| DATE | Depends – DATE or TIMESTAMP |
| TIMESTAMP | TIMESTAMP |
| LONG | TEXT |
| CHAR(n) | CHAR(n) |
| CLOB | TEXT |
| BLOB | BYTEA |
| RAW(n) | BYTEA |
| XMLTYPE | XML |