NULL in Oracle vs. NULL in PostgreSQL

Database migration is conceptually though a simple task, but there are many uninvited challenges that database migration brings in. You can read our article Challenges during a typical data migration project to know more about Oracle to PostgreSQL database migration challenges.

Besides all challenges, there are handful of code issues that you can face during database migration. This article explains how you can be watchful with NULL while doing code conversion from Oracle database to PostgreSQL database.

NULL in PostgreSQL database

NULL in PostgreSQL basically means “undefined”. Many believe that NULL means “empty” but that is not the ideal way to understand NULL. This is because when we say “empty” that means we know there is no value, but “undefined” simply means value is “unknown”. In PostgreSQL, use two single quotes without any space ( '' ) to specify an empty string and NULL keyword to assign column or variable to null.

Hence, below two statements are not the same in PostgreSQL.
var1 := NULL; --UNKNOWN
var1 := ''; --Empty

NULL in Oracle database

NULL in Oracle database means the absence of data. Oracle database upto 19c, treats NULL and empty string ( '' ) equivalent. But you must understand, empty string is a character value with a length of zero, whereas NULL is “unknown” as you don’t assign anything.

Hence, below two statements are identical in Oracle database.
var1 := NULL; --UNKNOWN
var1 := ''; --Empty

Words from Oracle documentation:

Oracle Database currently treats a character value with a length of zero as NULL. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls. Visit Oracle documentation for getting more information on how Oracle 19c treats NULL.

Examine the facts with NULL

Deep dive into NULL in PostgreSQL and ways to handle is a very useful and worth reading article explaining behavior of NULL through practical examples. Let’s explore some more here.

OraclePostgreSQL

Oracle database treats NULL and empty string equivalent and hence empty strings and NULL are not comparable. Keeping this concept in back of your mind, following examples will help boost your understanding towards NULL.

SQL> SELECT 'True' result FROM DUAL WHERE '' = '';

no rows selected
SQL> SELECT 'True' result FROM DUAL WHERE '' = NULL;

no rows selected
SQL> SELECT 'True' result FROM DUAL WHERE NULL = NULL;

no rows selected
SQL> SELECT 'True' result FROM DUAL WHERE coalesce('', '') = coalesce('', '');

no rows selected
SQL> SELECT 'True' result FROM DUAL WHERE coalesce('', '0') = coalesce('', '0');

RESULT
----------
True
SQL> SELECT 'True' result FROM DUAL WHERE coalesce('', '0') = coalesce(NULL, '0');

RESULT
----------
True
SQL> SELECT 'True' result FROM DUAL WHERE coalesce(NULL, '0') = coalesce(NULL, '0');

RESULT
----------
True

PostgreSQL treats NULL and empty string different, but all empty strings are equivalent. This means empty strings comparison returns “True” whereas NULL is not comparable to anything. Also you should understand that coalesce function handles NULL, but not empty strings. With those concepts in mind, analyze following examples and the correct way to operate with NULL.

postgres=# SELECT 'True' result WHERE '' = '';
 result 
--------
 True
(1 row)
postgres=# SELECT 'True' result WHERE '' = NULL;
 result 
--------
(0 rows)
postgres=# SELECT 'True' result WHERE NULL = NULL;
 result 
--------
(0 rows)
postgres=# SELECT 'True' result WHERE coalesce('', '') = coalesce('', '');
 result 
--------
 True
(1 row)
postgres=# SELECT 'True' result WHERE coalesce('', '0') = coalesce('', '0');
 result 
--------
 True
(1 row)
postgres=# SELECT 'True' result WHERE coalesce('', '0') = coalesce(NULL, '0');
 result 
--------
(0 rows)
postgres=# SELECT 'True' result WHERE coalesce(NULL, '0') = coalesce(NULL, '0');
 result 
--------
 True
(1 row)

Migrating Oracle NULL to PostgreSQL

NULL in databases is very commonly used feature and almost all functions, operators and other constructs behave in different way with respect to NULL. This is the reason why NULL handling is an important concept to remember while migrating Oracle database to PostgreSQL database. You can read our NULL Handling chapter to get more insight into NULLs and their semantically and behaviourally difference.

Following are some key points to remember during Oracle to PostgreSQL database migration requirements.

1. Do not use NULL to represent a value of zero, because they are not equivalent

You can explicitly assign NULL to a column or variable using assignment operator. If a variable or column is not assigned with any value then the column/variable value becomes NULL.

2. Use special operators IS NULL and IS NOT NULL to handle NULL

NULL is a special kind of internal generated value, which is identified by database implicit routines. NULL can’t be directly compared with any value or with any operator. Most databases provides IS NULL and IS NOT NULL to handle NULL.

3. NULL concatenation to a string

NULL in Oracle is considered as an empty string and when concatenated with another string, the result becomes the second string.

However in Postgres, value of NULL is “unknown” and when concatenated with another string, the result becomes also becomes “unknown” or NULL.

This is very basic but at the same time very very important concept to remember while migrating Oracle database to PostgreSQL. Always use Postgres CONCAT_WS function while converting code from Oracle to PostgreSQL.

4. NULL with aggregate functions

The general rule is, aggregate functions (SUM, AVG, MAX, MIN) will simply ignore NULL values. The only exception to the rule is COUNT function.

5. NULL handling in ORDER BY clauses

NULL values are especially tricky if you want to sort data. Usually NULL values appear at the end of a sorted list.

6. Inserting NULL in a table

As of Oracle 19c, behavior of NULL In Oracle and NULL in PostgreSQL are different. So, you must be very very careful while retrieving rows from tables and while doing Oracle to PostgreSQL code conversion.

Conclusion

In this article we tried to explain how to be careful with NULLs during migrating from Oracle to PostgreSQL.

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments