A common first question during a database migration is “How do Oracle datatypes compare to PostgreSQL?”

The simple answer is that they are very compatible, and map easily. The table below shows an Oracle to PostgreSQL data type comparison and mapping for the most common Oracle types. These basic data types just scratch the surface of what PostgreSQL can do – it has a very rich type system and supports custom types. Built-in types include JSON, arrays, UUID, IP addresses, geometric types, enumerations and much more.

Oracle Type PostgreSQL Type Comments
Char() Char()
Char(1) Char(1) If used as a boolean flag, use the boolean type instead
Varchar2() Varchar()
Timestamp Timestamptz In general, we recommend storing timestamps as time stamp with time zone (timestamptz), which is equivalent to Oracle’s timestamp with local time zone. This stores all values in UTC, even if the server or db client are in different timezones which avoids a lot of problems. But, some app code might have to be changed to timezone aware types – if significant, use the “timestamp” without timezone to minimize migration changes.
Date Timestamptz PostgreSQL “Date” type stores the “date” only – no time portion
Date Date
Number() Numeric() PostgreSQL Numeric is similar to Oracle Number with variable precision and scale so could be used for any numerical fields, but native integer and floating point fields are sometimes preferred.
Number(5,0) Integer Integer and Bigint perform better than Number() when used for joins of large tables, so consider mapping to Int for primary and foreign key fields commonly used for joins.
Number(10,0) Bigint
Number( ,2) Numeric( ,2)  PostgreSQL Numeric( ,2) is ideal for money types since it is exact precision (unless you’re dealing with Yen and need a ( ,0) type.  The “money” type is equivalent to numeric in precision but occasionally causes surprises for applications because of implicit assumptions about formatting. Never use a floating point representation (float/double) due to potential rounding during arithmetic.
CLOB Text Text is much easier to use, no LOB functions, just treat it as a character field. Can store up to 1GB of text.
Long Text
BLOB Bytea
Long raw
Raw
XMLTYPE XML
UROWID OID Check the application usage for UROWID, usage of OID in Postgres is discouraged, and does not provide the same performance benefit as ROWID used to in Oracle.

For a more comprehensive look at PostgreSQL data types and advanced features you can use, download our Data Types white paper.