PostgreSQL database treats NULL as unknown. NULL is not same as empty or zero value. This is why you can not compare NULL with anything in PostgreSQL database. Comparison of NULL with any value will always result NULL, which means an unknown (not empty) result.

Let’s take some examples around with NULL.

Comparing 0 = 0 returns True

postgres=# SELECT 0 = 0 result;
 result 
--------
 t
(1 row)

Comparing 0 <> 0 returns False

postgres=# SELECT 0 <> 0 result;
 result 
--------
 f
(1 row)

Comparing 0 = NULL returns NULL

postgres=# SELECT 0 = NULL result;
 result 
--------
 
(1 row)

Comparing 0 <> NULL returns NULL

postgres=# SELECT 0 <> NULL result;
 result 
--------
 
(1 row)

Comparing NULL = NULL returns NULL

postgres=# SELECT NULL = NULL result;
 result 
--------
 
(1 row)

Comparing NULL <> NULL returns NULL

postgres=# SELECT NULL <> NULL result;
 result 
--------
 
(1 row)

Comparing NULL = '' (empty string) returns NULL

postgres=# SELECT NULL = '' result;
 result 
--------
 
(1 row)

Comparing NULL <> '' (empty string) returns NULL

postgres=# SELECT NULL <> '' result;
 result 
--------
 
(1 row)

By now you must have understood, NULL is not comparable to anything, not even to itself in PostgreSQL. So, while coding you must carefully handle NULL using null handling functions available in PG.

But one thing is worth noticeable from above examples is, what does it mean by “Returns NULL”? Is NULL same as false? How do you verify if result returned is actually NULL or false or empty space? Let’s conclude that through some examples.

1 = 1 condition evaluates True and hence True is true returns True.

postgres=# SELECT (1 = 1) is true result;
 result 
--------
 t
(1 row)

1 = 0 condition evaluates False and hence False is true returns False.

postgres=# SELECT (1 = 0) is true result;
 result 
--------
 f
(1 row)

NULL = NULL returns NULL and hence NULL is true returns False.

postgres=# SELECT (NULL = NULL) is true result;
 result 
--------
 f
(1 row)

NULL = NULL returns NULL and hence NULL is false returns False.

postgres=# SELECT (NULL = NULL) is false result;
 result 
--------
 f
(1 row)

Verifying NULL existence using CASE

postgres=# SELECT CASE
postgres-#           WHEN result::text = ''::text THEN 'Result is empty'
postgres-#           WHEN result IS True THEN 'Result is true' 
postgres-#           WHEN result IS False THEN 'Result is false' 
postgres-#           WHEN result IS NULL THEN 'Result is null' 
postgres-#           ELSE 'None'
postgres-#        END "Null Check"
postgres-#   FROM (SELECT 1 = 1 result) a;
   Null Check   
----------------
 Result is true
(1 row)

postgres=# SELECT CASE
postgres-#           WHEN result::text = ''::text THEN 'Result is empty'
postgres-#           WHEN result IS True THEN 'Result is true' 
postgres-#           WHEN result IS False THEN 'Result is false' 
postgres-#           WHEN result IS NULL THEN 'Result is null' 
postgres-#           ELSE 'None'
postgres-#        END "Null Check"
postgres-#   FROM (SELECT 1 = 0 result) a;
   Null Check    
-----------------
 Result is false
(1 row)

postgres=# SELECT CASE
postgres-#           WHEN result::text = ''::text THEN 'Result is empty'
postgres-#           WHEN result IS True THEN 'Result is true' 
postgres-#           WHEN result IS False THEN 'Result is false' 
postgres-#           WHEN result IS NULL THEN 'Result is null' 
postgres-#           ELSE 'None'
postgres-#        END "Null Check"
postgres-#   FROM (SELECT 1 = NULL result) a;
   Null Check   
----------------
 Result is null
(1 row)

Handle NULLs in SQL queries

PostgreSQL provides following null handling functions and operators to deal with NULL in queries.

• IS NULL
• IS NOT NULL
• COALESCE
• CASE

Many a times you encounter string concatenation in queries and columns might be nullable. In those scenarios NULL handling is very very tricky and you must be very careful. Read String concatenation in PostgreSQL article to understand the complications around string concatenation in PostgreSQL involving NULLs.

If you are familiar with Oracle database then you may find this article bit confusing. Read NULL in Oracle vs. NULL in PostgreSQL article to understand how NULL in PostgreSQL behaves compared to Oracle database.

If you are working on Oracle to PostgreSQL database migration requirements and confused with NULL handling behavior, you can read our NULL Handling chapter to get more insight into NULLs and their semantically and behaviourally difference.

Conclusion

The purpose of this post is to make you understand about the fact about NULL in PostgreSQL and handle NULLs carefully when writing queries to avoid erroneous results.