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.