Migration from Oracle to Postgres

Data Type Mapping

PostgreSQL has rich set of data types. Some of the important Data type conversion between Oracle and PostgreSQL is as follow.

VARCHAR2(n)VARCHAR(n)In Oracle ‘n’ is number of bytes whereas in PostgreSQL ‘n’ is number of characters
CHAR(n)CHAR(n)In Oracle ‘n’ is number of bytes whereas in PostgreSQL ‘n’ is number of characters
NUMBER(n,m)NUMERIC(n,m)NUMBER type can be converted to NUMERIC but if you use SMALLINT, INT and BIGINT then performance would be better.
DATETIMESTAMP(0)Both databases has DATE type but Oracle DATE type returns date and time whereas PostgreSQL DATE type return only date no time.
TIMESTAMP WITH LOCAL TIME ZONETIMESTAMPTZThe PostgreSQL type Timestamptz(Timestamp with time zone) is different from the Oracle Timestamp with time zone. It is equivalent to Oracle’s Timestamp with local time zone, but this small difference can cause performance issue or application bug.
CLOBTEXTPostgreSQL TEXT type can store up to 1 GB of text.
BYTEA(1 GB limit)
Large object
In Oracle, BLOB datatype stores unstructured binary data in the database. BLOB type can store up to 128 terabytes of binary data. PostgreSQL BYTEA stores binary data but only upto 1 GB. If the data if above 1 GB then use Large object.

Leave a Reply

Your email address will not be published. Required fields are marked *