Handling NULL Values in PostgreSQL

PostgreSQLWhat is NULL?

There is often some confusion about NULL value, as it is treated differently in different languages. So there is an obvious need to clarify what NULL is, how it works in different languages, and what the actual value is behind the NULL. Before going into details, there is also a need to understand the concept of Three-valued logic[1] and Two-valued logic known as bivalent[2]. The bivalent is a  concept of boolean value where value can be true or false, but contrary to bivalent the Three-valued logic can be true, false, or (intermediate value) unknown. Now, back to NULL. In some languages NULL acts as bivalent, and in others, Three-valued logic (especially in databases).

C/C++

In “C/C++” the NULL is defined as 0 in the “stddef.h” which is included <cstddef> in case of C++ and stdlib.h in case of C.

The value can be tested against NULL directly by using the equality operators “==” or !=. Now take an example program and try to check the values against NULL in C.

The output of the above program will be “NULL is 0”, so it is quite evident that NULL is defined as “0” in C language.

Java

Contrary to C where NULL is 0, in Java NULL means the variable references do have value. The value can be tested against NULL by equality operators. When we print the null value, it will print the null. In Java, null is case sensitive and it must be in all small letters as “null”.

PostgreSQL

In PostgreSQL, NULL means no value. In other words, the NULL column does not have any value. It does not equal 0, empty string, or spaces. The NULL value cannot be tested using any equality operator like “=” “!=” etc. There are some special statements to test the value against NULL, but other than that, no statement can be used to test the NULL value.

Let’s do some interesting comparisons, which will clear up the concept of NULL in PostgreSQL. In the following code snippet, we are comparing 1 with 1 and the obvious result is “t” (TRUE). This leads us to understand that the PostgreSQL equality operator gives us true when two values match. Similarly, the equality operator works for the textual value.

Let’s do some more experiments, comparing NULL with NULL. If NULL is a normal value, then the result should be “t”. But NULL is not a normal value, therefore, there is no result of that.

Let’s compare NULL with NULL using an in-equality operator.  The result is the same as what we got previously. That proves that we cannot compare NULL with NULL using equality and inequality operators.

Similarly, no mathematical operation can be performed on NULL. PostgreSQL produces nothing when any NULL is used as an operand.

 

How to Use NULL

Therefore it is proved that NULL cannot be compared with any value using equality operators. Then how we can use the NULL if we cannot use any operator or mathematical operation? PostgreSQL provides special statements and functions to check and test values against NULL. There is the only way to use the NULL in PostgreSQL.

IS NULL/IS NOT NULL

COALESCE

PostgreSQL has a function name “COALESCE” [3]. The function takes n number of arguments and returns the first, not null arguments. You can test your expression against NULL using the function.

NULLIF

There is another function called “NULLIF”[3],  returns NULL if first and second arguments are equal, otherwise returns the first argument, here is the example where we are comparing 10 with 10 and we already know that these are equal so it will return NULL. In the second example, we are comparing 10 with 100 and in that case, it will return 10 the first value.

 

Usage of NULL

If NULL does not have any value, then what is the advantage of NULL? Here are some examples of usage:

In case a field does not have any value, and for example, we have database fields with first/middle and last name. Does, in reality, everybody have a first/middle and last name? The answer is no, there shouldn’t be a field that cannot have any value.

Let’s select students who do have a middle name. Does that query work here? No, and the reason behind it is the same we have discussed in earlier queries.

Let’s select by using the proper statements