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 more about the challenges during a typical data migration project in the below article.

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 I think 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 site for more context.

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.

Watch some features of NULL…

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.

Leave a Reply

Please Login to comment
Notify of
Close Menu
error: Content is protected !!