(updated 2023-05-08) For those of you out there working with PostgreSQL in a professional way, migrating from Oracle to PostgreSQL might be one of the most beloved tasks available. One of the first things most people will notice, however, is that the datatypes available in Oracle might not be quite the same in PostgreSQL. This blog will try to shed some light on the differences between Oracle and PostgreSQL datatypes. It provides you with a list of how they compare.

Datatypes in Oracle and PostgreSQL

While there are many similarities between Oracle and PostgreSQL, there are a couple of differences which are quite noteworthy. The first thing many people might notice is: PostgreSQL has many more data types than Oracle. As of version 10.0 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 used for internal purposes.

Still: At the end of the day there are just more data types which can be used by applications.

Let’s take a look and see which types can be matched. The following table contains a lot of potential options:

Oracle typePossible PostgreSQL types
CHARchar, varchar, text
NCHARchar, varchar, text
VARCHARchar, varchar, text
VARCHAR2char, varchar, text, json
NVARCHAR2char, varchar, text
CLOBchar, varchar, text, json
LONGchar, varchar, text
RAWuuid, bytea
BFILEbytea (read-only)
NUMBERnumeric, float4, float8, char, varchar, text
NUMBER(n,m) with m<=0numeric, float4, float8, int2, int4, int8, boolean, char, varchar, text
FLOATnumeric, float4, float8, char, varchar, text
BINARY_FLOATnumeric, float4, float8, char, varchar, text
BINARY_DOUBLEnumeric, float4, float8, char, varchar, text
DATEdate, timestamp, timestamptz, char, varchar, text
TIMESTAMPdate, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONEdate, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH LOCAL TIME ZONEdate, timestamp, timestamptz, char, varchar, text
INTERVAL YEAR TO MONTHinterval, char, varchar, text
INTERVAL DAY TO SECONDinterval, char, varchar, text

As you can see, all types can always be represented as text and as varchar. However, this is of course not desirable. What's more, in Oracle there is no such thing as an integer. Everything is represented as number. For example: In Oracle you might see number (5, 0). The way to map this is to use integer or bigint on the PostgreSQL side. In general, “CPU data types” such as integer and bigint are much faster than numeric.

A second important thing is varchar2: On the PostgreSQL side it can easily be mapped to varchar or text. A small detail is that in Oracle, varchar2 can be the number of bytes or the number of characters. Consider the following example: VARCHAR2(20 BYTE) vs. VARCHAR2(10 CHAR). You don't have to think twice about what to do on the PostgreSQL side, because in Postgres we are always talking about “characters”.

Oracle → PostgreSQL: Automatic conversion

Of course there is no need to do most of the work by hand. The CYBERTEC Migrator automatically maps data types for you. The Migrator also helps with indexes and constraints, and is constantly updated with robust new features to make your migration as easy and as fast as possible. You can get a FREE download of the Standard edition to try it out. The Migrator is also available on the SUSE Marketplace, which features products that are particularly robust and compatible with SUSE and Rancher software.

For further information about data types, see: