(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 type | Possible PostgreSQL types |
|---|---|
CHAR | char, varchar, text |
NCHAR | char, varchar, text |
VARCHAR | char, varchar, text |
VARCHAR2 | char, varchar, text, json |
NVARCHAR2 | char, varchar, text |
CLOB | char, varchar, text, json |
LONG | char, varchar, text |
RAW | uuid, bytea |
BLOB | bytea |
BFILE | bytea (read-only) |
LONG RAW | bytea |
NUMBER | numeric, float4, float8, char, varchar, text |
NUMBER(n,m) with m<=0 | numeric, float4, float8, int2, int4, int8, boolean, char, varchar, text |
FLOAT | numeric, float4, float8, char, varchar, text |
BINARY_FLOAT | numeric, |
BINARY_DOUBLE | numeric, float4, float8, char, varchar, text |
DATE | date, timestamp, timestamptz, char, varchar, text |
TIMESTAMP | date, timestamp, timestamptz, char, varchar, text |
TIMESTAMP WITH TIME ZONE | date, timestamp, timestamptz, char, varchar, text |
TIMESTAMP WITH LOCAL TIME ZONE | date, timestamp, timestamptz, char, varchar, text |
INTERVAL YEAR TO MONTH | interval, char, varchar, text |
INTERVAL DAY TO SECOND | interval, char, varchar, text |
XMLTYPE | xml |
MDSYS.SDO_GEOMETRY | geometry |
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:
- This blog article about Query Parameter Data Types and Performance by Laurenz Albe
- Or read about UNION ALL, data types and performance issues.
- You can also visit our Data Types Blog Archive.