(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.
Table of Contents
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”.
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.
I think ora2pg should also be mentioned as a trustworthy tool to do correct data mapping between Oracle and PostgreSQL.
Hi,
I have difficulties finding the way to implement Oracle collections in Postgres. In particular, I am looking for the way to implement Oracle TYPE as TABLE OF TYPE. For example:
Oracle definition:
CREATE TYPE names (id number, name VARCHAR2(30));
CREATE TYPE t_names AS TABLE OF names;
I have no issues creating such types in PostgreSQL, however I cannot find any documentation on how to manipulate data in TABLE OF TYPE collections or how to pass such type between functions in . PostgreSQL.
Any help would be greatly appreciated
The PostgreSQL equivalent would be to create a composite type (just like in your first statement) and then use an array (type
names[]).It is not exactly the same, but a close approximation.
Hi Laurenz,
Thank you for your reply. I am using this type of declaration names[] and i think i know how to load it with the multiple occurrences of data to simulate the table of type, however I do not know how to manipulate the data in this collection names[]. I would greatly appreciate a sample of code showing for example how to print all the records in this collection, or even better how to navigate through the collection looking for a particular value in any record/column. Thank you.
Hi,
I want to know under what scenario number datatype of oracle gives numeric or double precison of postgresq?
Basically i want to know what are conditions or reason behind the datatypes getting changed while conversion from oracle to postgresql?
That is impossible to answer unless you tell us what changes these data types.
Oracle's
NUMBERis equivalent to PostgreSQL'snumeric, but if you don't mind imprecision and want fast arithmetic, you can also usedouble precision.How can I contact you ? Because I want to know more in depth about the data types getting convert while migration taking place between Oracle and PostgreSQL.
please do provide some contact details.
Thank You.
sales@cybertec.at can sell you a few hours of consulting time.
Hi Team,
I face some issue, column mapping oracle database to postgresql