Oracle Data Type Mapping with PostgreSQL

One of the first and most common questions in the process of migrating a database from Oracle to PostgreSQL is “How are Oracle data types comparable to PostgreSQL types?”.

The simple answer is that they are very compatible and easily matched. The table below shows the comparison and mapping of Oracle data types to PostgreSQL for the most common Oracle types. These basic data types only superficially demonstrate what PostgreSQL can do — it has a very rich type system and supports custom types. The built-in types include JSON, arrays, UUIDs, IP addresses, geometric types, enums, and more .

Oracle data type	PostgreSQL data type	Comments
Char ()	Char ()
Char (1)	Char (1)	If used as a boolean flag, it is better to use the data type boolean
Vararch2 ()	Vararch ()
Timestamp	Timestamptz	In general, we recommend storing the timestamp as a timestamp with a time zone (timestamptz), which is equivalent to an Oracle timestamp with a local time zone. In this way, all values ​​in UTC are saved, even if the database server or client is located in different time zones, which helps to avoid many problems. But maybe some application code should use types that have time zone information — if this is an important point, use a timestamp without a time zone to minimize migration changes.
Date	Timestamptz	PostgreSQL type “Date” stores only the date and does not store time.
Date	Date
Number ()	Numeric ()	PostgreSQL Numeric is similar to Oracle Number with a variable range and precision, so it can be used for any numeric fields, but sometimes integer fields and floating point numbers are preferred.
Number (5.0)	Integer	Integer and Bigint work better than Number () when used for joins in large tables, so it is preferable to map to Int for the primary and foreign key fields commonly used for join.
Number (10.0)	Bigint
Number (, 2)	Numeric (, 2)	PostgreSQL Numeric (, 2) is ideally suited for money types, as it has specific accuracy (if you are not dealing with a yen and do not need the type (, 0)). The “money” type is equivalent to numeric in accuracy, but sometimes causes surprises for applications due to implicit formatting assumptions. Never use float / double representation because of potential rounding during arithmetic.
CLOB	Text	Text is much easier to use, without LOB functions, just consider it as a character field. Can store up to 1 GB of text.
Long	Text
Blob	Bytea
Long raw
Raw
XMLTYPE	XML
UROWID	OID	Postgres' OID usage is not equivalent and does not provide the same performance advantage as the ROWID used in Oracle.