What You should know
# 03.01. TransactionsOracle database always uses transactions but in PostgreSQL you have to activate that. In Oracle, the transaction starts when executing any statement and ends when COMMIT statement executed. In PostgreSQL, transaction starts when execute BEGIN and end when COMMIT statement executed. Even the isolation levels also have no problem. PostgreSQL database knows all the isolation levels that Oracle database knows.
Oracle:DELETE FROM table_name WHERE id = 120;COMMIT;PostgreSQL:BEGIN;DELETE FROM table_name WHERE id = 120;
COMMIT;
# 03.02. TO_DATE
Oracle’s TO_DATE function return DATE type value(year, month, day, hour, minute, second).
PostgreSQL’s TO_DATE return DATE type value(year, month, day).The solution for this incompatibility is to convert TO_DATE() to TO_TIMESTAMP().Oracle:SELECT TO_DATE ('20180314121212','yyyymmddhh24miss') FROM dual;PostgreSQL:
SELECT TO_TIMESTAMP ('20180314121212','yyyymmddhh24miss')::TIMESTAMP(0);
# 03.03. DUAL
The DUAL virtual table does not exist in PostgreSQL.
# 03.04. PACKAGE
The CREATE PACKAGE command is not directly supported in PostgreSQL. The only remotely similar thing would be to create one schema for each "package" and put all functions of one package into that schema.
# 03.05. External Coupling +
Oracle uses + operator for left and right join but PostgreSQL does not use it.Oracle:SELECT a1.name1,a2.name2 FROM a1, a2 WHERE a1.code = a2.code(+);PostgreSQL:
SELECT a1.name1, a2.name2 FROM a1 LEFT OUTER JOIN a2 ON a1.code = a2.code;
# 03.06. EXTERNAL TABLE
Oracle EXTERNAL TABLE does not exists internally into PostgreSQL. Ora2Pg will export them as remote tables using extension file_fdw
# 03.07. External modules (DBMS)
Oracle specific code always need to be rewritten:- DBMS_OUTPUT– UTL_FILE– DBMS_PIPE
– DBMS_ALERT
– UTIL_SMTP
- ...
Oracle uses START WITH..CONNECT BY for hierarchical queries. PostgreSQL does not support START WITH..CONNECT BY statement. PostgreSQL have WITH RECURSIVE for hierarchical queries so translate CONNECT BY statement into WITH RECURSIVE statement.
The behavior of SUBSTR function in Oracle and PostgreSQL is different. The SUBSTR function works in PostgreSQL without error but returns a different result. This difference can cause application bugs.
Oracle:
SELECT SUBSTR('ABC',-1) FROM DUAL;
Returns 'C'
PostgreSQL:
postgres=# SELECT SUBSTR('ABC',-1);
substr
--------
ABC
(1 row)
# 03.10. Behavior of Empty String and NULL
In Oracle, empty strings and NULL values in string context are the same. The concatenation of NULL and string obtain string as a result. In PostgreSQL the concatenation result is null in this case. In Oracle IS NULL operator is used to check whether string is empty or not but in PostgreSQL result is FALSE for empty string and TRUE for NULL.
