Mapping Oracle datatypes to PostgreSQL

05.2018 / Category: / Tags: | |

(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, 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 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

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:

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
Amarnath Bichchal
Amarnath Bichchal
2 years ago

Hi Team,

I face some issue, column mapping oracle database to postgresql

3 years ago

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?

3 years ago
Reply to  PRIYA GOUR

That is impossible to answer unless you tell us what changes these data types.
Oracle's NUMBER is equivalent to PostgreSQL's numeric, but if you don't mind imprecision and want fast arithmetic, you can also use double precision.

3 years ago
Reply to  laurenz

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.

3 years ago
Reply to  PRIYA GOUR can sell you a few hours of consulting time.

Andre Rusanoff
Andre Rusanoff
4 years ago

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

4 years ago
Reply to  Andre Rusanoff

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.

Andre Rusanoff
Andre Rusanoff
4 years ago
Reply to  laurenz

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.

5 years ago

I think ora2pg should also be mentioned as a trustworthy tool to do correct data mapping between Oracle and PostgreSQL.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram