CYBERTEC PostgreSQL Logo

Oracle to PostgreSQL: Technical quick guide

CYBERTEC offers comprehensive services to move from Oracle to PostgreSQL. Intelligent tooling is available on our website. We want to help you to move to PostgreSQL faster. This quick-start migration guide will get you rapidly moving in the right direction.

There are various aspects you need to take into consideration when moving from Oracle to PostgreSQL, which include:

  • Migrating data structures
  • Migrating data
  • Converting common SQL statements
  • Converting PL/SQL to PL/Java
  • Storage management in PostgreSQL and Oracle

Let’s take a look at these vital points in detail.

Migrating data structures

The first thing you will see is that PostgreSQL has many more data types than Oracle. It is therefore important to make the right choices. Selecting the right data type can massively impact performance.

If you are not sure how to map Oracle data types to PostgreSQL, we have compiled a conversion matrix. The matrix shows you which types can be mapped to what counterpart.

Oracle typePossible PostgreSQL type
CHARchar, varchar, text
NCHARchar, varchar, text
VARCHARchar, varchar, text
VARCHAR2char, varchar, text, json, jsonb
NVARCHAR2char, varchar, text
CLOBchar, varchar, text, json, jsonb
LONGchar, varchar, text
RAWuuid, bytea
BLOBbytea
BFILEbytea (read-only)
LONG RAWbytea
NUMBERnumeric, float4, float8, char, varchar, text
NUMBER (n, m) with m<=0numeric, 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
DATEdate, timestamp, timestamptz, char, varchar, text
TIMESTAMPdate, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONEdate, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH LOCAL TIME ZONEdate, timestamp, timestamptz, char, varchar, text
INTERVAL YEAR TO MOTHinterval, char, varchar, text
INTERVAL DAY TO SECONDinterval, char, varchar, text
XML TYPExml, char, varchar, text
MDSYS.SDO_GEOMETRYgeometry (see “PostGIS support” below)

Ideally, you will decide on a data type that provides the least overhead. What does that mean? Here is an example: number(10, 0) can be mapped to numeric(10, 0) but you can also map it to int8. Using int8 is usually a lot more efficient. Int8 will definitely eat 8 bytes – however, calculations are a lot faster if you use int8 rather than numeric.

varchar2 can be mapped directly to varchar or the text data type. However, we have found it useful to consider the “citext” extension in PostgreSQL. The idea behind citext is to have a case insensitive text data type, which makes searching a lot more convenient. In web applications, case insensitive search is usually what you are looking for. If you are generally interested in fuzzy search, consider checking out our blog post about this exceptionally important topic. A migration is usually a good chance to clean up and improve things.

BLOBs are also an issue many people keep asking about. In general, PostgreSQL offers the bytea data type (“byte array”). It feels like a text column but it can actually hold binary data. If you don’t want to use a column to store BLOBs, it is also possible to use the PostgreSQL BLOB interface directly.

NOTE: What is important to mention here is that Oracle Spatial can be mapped to PostGIS and PostgreSQL. So if you are using GIS data there is a way to migrate to PostgreSQL as well. There are no practical limitations in that case, either.

To map data types automatically, consider checking out CYBERTEC Migrator. It has advanced data type mapping capabilities and offers an easy way to predict the best possible data type to store large volumes of data in PostgreSQL.

CREATE SYNONYM: The missing beauty

Oracle provides the CREATE SYNONYM command which is not available in PostgreSQL. Some time ago, we at CYBERTEC implemented CREATE SYNONYM for PostgreSQL, but this modification was rejected because it does not fit into the overall concept of PostgreSQL.

  • Synonyms for tables: Use a view instead (SELECT * …)
  • Synonyms for views: Use a view on top of the view (SELECT * …)
  • Synonyms for functions / procedures: Use wrapper functions
  • Synonyms for materialized views: Use views

In general, it is better to get rid of these synonyms during migration. It makes future development a lot easier. Setting the search_path has proven to be generally useful, and often reduces the need to use synonyms in the first place.

Invalid views in Oracle – valid views in PostgreSQL

When migrating data structures from Oracle to PostgreSQL, you will notice that PostgreSQL uses strict dependency tracking. Let me provide you with an example: If a table is dropped, PostgreSQL will automatically drop all of its dependent objects. In Oracle this is not the case. You might have to deal with invalid views, which is an issue you should be aware of during a migration project.

Migrating data into PostgreSQL

Once the data structures found in Oracle have been moved to PostgreSQL, it is time to think about moving the data in general.

A lot of stuff can be done by the migration tool, but there are some things that need some attention on the Oracle database side as well. Here are some of the most important aspects:

  • Infinite numbers (“minus infinite” and “plus infinite”)
    • Can be mapped to “infinity” if double precision is used (which might not be what you want, after all)
  • Corrupted strings in Oracle
    • Invalid byte sequence for encoding “UTF8”: 0x80
    • Corrupted strings are more common than you might think.
    • It is important to have a plan to handle those
  • Zero bytes in Oracle
    • Invalid byte sequence for encoding “UTF8”: 0x00
    • Invalid sequences are pretty common too

As previously stated, most of the above problems cannot easily be solved during the migration process, but need some pre-treatment on the source side. Of course, the migration tool is able to fix many issues, but not all of them. You need to be aware of that.

When data is read from Oracle, it has to be done in a single transaction to ensure consistency (or at least in many transactions capable of seeing the same snapshot of data as it is done by the CYBERTEC Migrator). To achieve this, you will end up running a long transaction on Oracle. This can be a problem in and of itself, because Oracle terminates a transaction in the case of “ORA-01555: snapshot too old”.

The solution is to configure it so that it can handle very long transactions before you start the migration of data. The way to do this is to use really large UNDO tablespaces.

Moving data is also a bit tricky. In Oracle, you will find a couple of restrictions in the OCI interface which can lead to very poor performance when large fields are migrated to PostgreSQL. Those limitations and performance issues are caused by the way Oracle reads data, and in many cases, client code cannot really do much about them.

PostgreSQL and tablespaces

In Oracle, the concept of a “tablespace” is highly important. One could argue that Oracle is basically implementing its own filesystem. In contrast to that, PostgreSQL relies more heavily on operation system functionality (POSIX). Resist the urge to create tablespaces during migration. Tablespace does not make much sense in the PostgreSQL world.

Additionally, in a modern cloud environment, tablespaces are mostly a thing of the past. So if you are creating a new infrastructure for PostgreSQL on Kubernetes, such as provided by Scalefield, there is no need to use tablespaces at all.

Converting common SQL statements

Oracle has some specialities in its flavor of SQL which have to be considered when migrating code from Oracle to PostgreSQL. This section shows some of the most common issues people encounter during their migrations.

Rewriting joins for PostgreSQL

Oracle uses a different syntax for outer joins than the rest of the world. You should keep that in mind when converting code from Oracle to PostgreSQL. The following example shows how this works.

Oracle

SELECT b.col1, a.col2
FROM base_table b, attributes a
WHERE b.id=a.b_id(+);

has to be translated to

PostgreSQL

SELECT b.col1, a.col2
FROM base_table b
LEFT JOIN attributes a ON b.id = a.b_id;

The change is actually easy, but quite annoying to do.

Subselects and aliases

In Oracle a subselect does not need a name. It can be “anonymous”:

SELECT * FROM (SELECT * FROM some_table)

You can easily rewrite this in PostgreSQL. The following code shows what the counterpart in the Open Source work would actually look like:

SELECT * FROM (SELECT * FROM some_table) AS your_fancy_name

Handling NULL and empty strings

Oracle treats NULL as an empty string. This has a couple of implications:
'hello' || NULL is not NULL in Oracle. Therefore, it has to be translated to the following SQL fragment in order to have the same semantics in PostgreSQL:

concat('hello', NULL)
or use
coalesce(strcol, '')

Migrating null statements can lead to subtle bugs that can be hard to track down. You should keep a sharp eye out for such details and explicitly check for them.

Time functions in Oracle and PostgreSQL

Most Oracle code uses proprietary functions such as …

  • SYSDATE
  • SYSTIMESTAMP

It’s necessary to translate those calls to suit PostgreSQL:

  • clock_timestamp()
  • current_date or current_timestamp

Fortunately, this change can easily be done. Search and replace is usually enough.

Migrating sequences to PostgreSQL

Oracle databases handle sequences quite differently. You’ll see the following method to produce the next value:

asequence.NEXTVAL

The PostgreSQL equivalent is:

nextval('asequence')

Both databases are not using the ANSI SQL way of fetching values from a sequence which would look like this: NEXT VALUE FOR asequence

No need for DUAL

In PostgreSQL, a query does not necessarily need a FROM-clause. It is perfectly fine not to use one. Therefore there is no need for DUAL.

SELECT * FROM dual;

… is not necessary in PostgreSQL. orafce can simulate DUAL, but it is better to avoid it on the PostgreSQL side. There is simply no need for it.

DECODE: The almighty

DECODE is an Oracle-specific thing:

decode(expr, search, result [, search, result...] [, default])

The way to change things in PostgreSQL is to …

  • Replace with CASE / WHEN
  • Replace with the “decode” function provided by orafce

Plain PostgreSQL works as follows:

CASE WHEN expr THEN expr [...] ELSE expr END

In general, DECODE can be replaced quite easily. It does not contain complex logic.

Handling recursions: CONNECT BY

In Oracle, there are two ways to handle recursions:

WITH RECURSIVE: New syntax
CONNECT BY: Old syntax

PostgreSQL has a full WITH RECURSIVE implementation. The old CONNECT BY syntax is highly Oracle-specific and cannot be used in PostgreSQL (or any other SQL database engine we are aware of).

Here is an example showing how CONNECT BY can be translated to WITH RECURSIVE:


SELECT empno, ename, level
FROM emp
START WITH empno = 7788
CONNECT BY PRIOR mgr = empno;

… versus …

WITH hierarchy (empno, ename, mgr, "level") AS (
SELECT empno, ename, mgr, 1 AS "level"
FROM emp
WHERE empno = 7788
UNION ALL
SELECT e.empno, e.ename, e.mgr, h."level" + 1
FROM hierarchy h
JOIN emp e ON h.mgr = e.empno
) SELECT empno, ename, "level" FROM hierarchy;

Converting PL/SQL and PL/Java

The most expensive part of every migration is to migrate the stored procedure code. While PL/pgSQL and PL/SQL are pretty similar, there are still differences that have to be taken into account. If you are using PL/Java on the Oracle side, you can make use of the PostgreSQL counterpart but we would suggest to rather replace the code completely. Running server-side Java is not too common in the PostgreSQL world.

PL/pgSQL is a clone of PL/SQL, but it is sufficiently different to require some attention (e.g., RETURNS vs.RETURN). The CYBERTEC Migrator has the means to automatically convert code.

Autonomous transaction in Oracle and PostgreSQL

PostgreSQL has no such thing as PRAGMA AUTONOMOUS_TRANSACTION. One has to work around this missing functionality. In general, it is better to replace autonomous transactions with savepoints and try to make sure that transactions actually can commit. In some rare cases, there is no way to work around autonomous transactions. The way to model that in PostgreSQL is to use the dblink extension.

BULK COLLECT: Use rows

BULK COLLECT is not available on the PostgreSQL side. The way to work around this in PostgreSQL is to use a row-by-row approach.

Be careful in this case. Efficiency might become an issue. It is therefore better to use SQL statements rather than loops.

Simulating Oracle functions

In many cases PostgreSQL offers the same functionality as Oracle. However, often the names of functions are simply different from each other.

The orafce extension for PostgreSQL implements many of those Oracle-specific functions in PostgreSQL. By using the extension, the number of manual code changes can be reduced dramatically.

The following listing shows some code samples and shows how things will be exposed to the end user:

SELECT last_day('2000-02-01 121143');
SELECT next_day ('2008-01-01 121212', 'sunAAA');
SELECT months_between ('2007-02-28 111111', '2007-04-30 112121');
select dbms_pipe.purge('bob');
select instr('Tech on the net', 'e') = 2;

orafce has been around for many years and is mature. It can be relied upon.

PL/SQL packages

In PostgreSQL, there is no concept of PL/SQL packages. However, there are ways to simulate packages. There are various ways to approach the problem:

  • Make use of CREATE EXTENSION – You can always pack code into extensions
  • Use schemas for each “package”

You will figure out that packages are not really an issue, and that you can easily live without them on the PostgreSQL side.

Migrating triggers to PostgreSQL

In PostgreSQL, a trigger will always call a function. The CREATE TRIGGER command does not contain the code directly:

demo=# \h CREATE TRIGGER
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ]
}
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ]
]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE

The beauty on the PostgreSQL side is that the code can easily be reused. What is also noteworthy here is that triggers to increment values are not needed. You can simply use DEFAULT columns to automatically determine the next value and set it.

LOGON triggers are not supported in PostgreSQL. If you need this type of functionality, it is necessary to shift the code to the application, or avoid the thing altogether.

Professional help

We at CYBERTEC have helped countless customers to migrate from Oracle to PostgreSQL. Furthermore, we offer the CYBERTEC Migrator to faciliatate the transition. Contact us today an receive your personal migration offer. We offer over 20 years of PostgreSQL experience, a professional handling and a timely delivery.

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

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


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

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram