There are some very basic syntax elements that work in Oracle, but they might not work in PostgreSQL. This section lists some of the most important pitfalls to take into consideration. Of course, this list is not complete by any means, but it should point you in the right direction.
In Oracle, you may come across the following statement:
DELETE mytable;
In PostgreSQL, this statement is wrong since PostgreSQL requires that you use a FROM clause in the DELETE statement. The good news is that this kind of statement is easy to fix.
The next thing you might find is the following:
SELECT sysdate FROM dual;
PostgreSQL has neither a sysdate function nor a dual function. The dual function part is easy to fix as you can simply create a VIEW returning one line. In Oracle, the dual function works as follows:
SQL> desc dual
Name Null? Type
----------------------------------------- -------- -
DUMMY VARCHAR2(1)
SQL> select * from dual;
D
-
X
In PostgreSQL, the same can be achieved by creating the following VIEW:
CREATE VIEW dual AS SELECT 'X' AS dummy;
The sysdate function is also easy to fix. It can be replaced with the clock_timestamp() function.
Another common problem is the lack of data types, such as VARCHAR2, as well as the lack of special functions that are only supported by Oracle. A good way to get around these issues is to install the orafce extension, which provides most of the stuff that's typically needed, including the most commonly used functions. It certainly makes sense to check out https://github.com/orafce/orafce to learn more about the orafce extension. It has been around for many years and is a solid piece of software.
A recent study has shown that the orafce extension helps ensure that 73% of all Oracle SQLs can be executed on PostgreSQL without modifications if the orafce extension is around (done by NTT).
One of the most common pitfalls is the way Oracle handles outer joins. Consider the following example:
SELECT employee_id, manager_id
FROM employees
WHERE employees.manager_id(+) = employees.employee_id;
This kind of syntax is not provided by PostgreSQL and never will be. Therefore, the join has to be rewritten as a proper outer join. The + is highly Oracle-specific and has to be removed when moving from MySQL or MariaDB to PostgreSQL
In this chapter, you have already learned some valuable lessons about how to move from databases such as Oracle to PostgreSQL. Migrating both MySQL and MariaDB database systems to PostgreSQL is fairly easy. The reason for this is that Oracle might be expensive and a bit cumbersome from time to time. The same applies to Informix. However, both Informix and Oracle have one important thing in common: the CHECK constraints are honored properly and data types are handled properly. In general, we can safely assume that the data in those commercial systems is somewhat correct and doesn't violate the most basic rules of data integrity and common sense.
Our next candidate is different. Many things you know about commercial databases aren't true in MySQL. The term NOT NULL doesn't mean much to MySQL (unless you explicitly use strict mode). In Oracle, Informix, Db2, and all the other systems I am aware of, NOT NULL is a law that is obeyed under all circumstances. MySQL doesn't take these constraints that seriously by default. (Though, to be fair, this has been changed in recent versions. Strict mode was not on by default up until very recently. However, many old databases still use the old default settings.)
In the case of migrations, this causes some issues. What are you going to do with data that is technically wrong? If your NOT NULL column suddenly reveals countless NULL entries, how are you going to handle that? MySQL doesn't just insert NULL values in NOT NULL columns. It will insert an empty string or zero based on the data type, so things can get pretty nasty.