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 those datatypes available in Oracle might not be quite the same in PostgreSQL. This blog will try to shed some light and show, how things work.
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 internal used for internal purposes.
Still: At the end of the day there are just more data types, which are can be used by applications, which of course
Let us 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, float4, float8, char, varchar, text |
| 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 | date, timestamp, timestamptz, char, varchar, text |
| LOCAL TIME ZONE | |
| INTERVAL YEAR TO MONTH | interval, char, varchar, text |
| INTERVAL DAY TO SECOND | interval, char, varchar, text |
| MDSYS.SDO_GEOMETRY | geometry (see “PostGIS support” below) |
As you can see all types can always be represented as text and as varchar. However, this is of course not desirable. However, there is more: In Oracle there is no such thing as “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 have to think twice, 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 most of the work by hand. The ora_migrator tool (https://github.com/cybertec-postgresql/ora_migrator) will use the oracle_fdw (= database link to Oracle) to map data types for you. ora_migrator will also help with indexes, constraints, and so on to make migrations as easy and as fast as possible. Also have a look on our Cybertec Enterprise Migrator tool page.