This blog was first authored by Ibrar Ahmed in 2020. We’ve updated it in 2025 for clarity and relevance, reflecting current practices while honoring their original perspective.
Picture this: you’re running what should be a simple query, and PostgreSQL returns completely unexpected results. You double-check your syntax, verify your data, and everything looks correct. The culprit? Those sneaky NULLs hiding in your dataset, behaving nothing like the null values you’re used to in other programming languages.
NULLs in PostgreSQL can trip up even experienced developers. They’re not the same as empty strings or zeros; NULL represents the complete absence of a value. This fundamental difference makes them behave in ways that might surprise you until your queries start returning those head-scratching results.
This post breaks down exactly how PostgreSQL handles NULLs in different contexts and shows you the tools to work with them confidently.
Most developers encounter NULL early in their careers, but here’s the thing to know: its meaning changes dramatically from language to language. If you’re coming to PostgreSQL from a C or Java background, you might instinctively treat NULL as just another value, and that’s where things go sideways.
Let’s clarify this with two fundamental logic models: bivalent (only TRUE or FALSE) and three-valued (TRUE, FALSE, or UNKNOWN). Many programming languages stick to bivalent logic, but SQL—and therefore PostgreSQL—operates on three-valued logic. This is the key to understanding why NULL behaves the way it does.
To really grasp PostgreSQL’s approach, let’s see how other languages handle null values.
In C and C++, the standard headers define NULL as 0. This means you can compare it using == or != just like any other numeric constant.
|
1 |
#if defined (_STDDEF_H) || defined (__need_NULL)<br>#undef NULL /* in case <stdio.h> has defined it. */<br>#ifdef __GNUG__<br>#define NULL __null<br>#else /* G++ */<br>#ifndef __cplusplus<br>#define NULL ((void *)0)<br>#else /* C++ */<br>#define NULL 0<br>#endif /* C++ */<br>#endif /* G++ */<br>#endif /* NULL not defined and <stddef.h> or need NULL. */<br>#undef __need_NULL |
A quick test program shows this in action:
|
1 |
#include <stddef.h><br>#include <stdio.h><br>void main()<br>{<br> if ('0' == NULL)<br> printf("NULL is '0' n");<br> if ("" == NULL)<br> printf("NULL is empty string n");<br> if (' ' == NULL)<br> printf("NULL is space n");<br> if (0 == NULL)<br> printf("NULL is 0 n");<br>} |
The output confirms that in C, NULL is simply 0.
Java takes a different approach, treating null as a special reference value rather than a number. You still use the same equality operators for comparison, but when you print it, you’ll see the literal word “null” (always lowercase in Java).
|
1 |
public class Test<br>{ <br> public static void main (String[] args) throws java.lang.Exception<br> {<br> System.out.println("Null is: " + null);<br> }<br>}<br>Null is: null |
PostgreSQL’s NULL means “no value.” It is not 0, an empty string, or a single space, and it won’t cooperate with ordinary equality operators. Instead, PostgreSQL follows three-valued logic: any comparison involving NULL yields UNKNOWN, which in turn behaves like FALSE in WHERE clauses.
Normal numeric comparison:
|
1 |
postgres=# SELECT 1 = 1 result;<br> result <br>--------<br> t<br>(1 row) |
Normal text comparison:
|
1 |
postgres=# SELECT 'foo' = 'foo' result;<br> result <br>--------<br> t<br>(1 row) |
Compare NULL with NULL:
|
1 |
postgres=# SELECT NULL = NULL result;<br> result <br>--------<br><br><br>(1 row) |
Inequality doesn’t help:
|
1 |
postgres=# SELECT NULL != NULL result;<br> result <br>--------<br> <br><br>(1 row) |
Math operations are equally fruitless:
|
1 |
postgres=# SELECT NULL * 10 is NULL result;<br> result <br>--------<br> t<br>(1 row)<br> |
Since ordinary operators don’t work with NULL, PostgreSQL provides specialized tools designed specifically for these situations.
|
1 |
postgres=# SELECT NULL is NULL result;<br> result <br>--------<br> t<br>(1 row) |
|
1 |
postgres=# SELECT NULL is NOT NULL result;<br> result <br>--------<br> f<br>(1 row)<br> |
COALESCE returns the first non-NULL argument from a list. This is incredibly useful for providing fallback values:
|
1 |
COALESCE (NULL, 2 , 1); |
NULLIF(a, b) returns NULL if a equals b; otherwise, it returns a. This is handy when you want to convert specific values to NULL:
|
1 |
postgres=# SELECT NULLIF (10, 10);<br>nullif<br>--------<br> <br><br>(1 row)<br><br>postgres=# SELECT NULLIF (10, 100);<br>nullif<br>--------<br> 10<br>(1 row) |
Let’s look at some real-world scenarios where NULL handling becomes crucial.
Consider a student table where middle names are optional:
|
1 |
postgres=# CREATE TABLE student(id INTEGER, fname TEXT, sname TEXT, lname TEXT, age INTEGER);<br><br>postgres=# SELECT * FROM STUDENT;<br> id | fname | sname | lname | age <br>----+-------+-------+-------+-----<br> 1 | Adams | Baker | Clark | 21<br> 2 | Davis | | Evans | 22<br> 3 | Ghosh | Hills | | 24<br>(3 rows) |
If you try searching for empty strings, you’ll miss the NULL middle name:
|
1 |
postgres=# SELECT * FROM STUDENT WHERE sname = '';<br> id | fname | sname | lname | age <br>----+-------+-------+-------+-----<br>(0 rows) |
Instead, use IS NULL to find records with missing middle names:
|
1 |
postgres=# SELECT * FROM STUDENT WHERE sname IS NULL;<br> id | fname | sname | lname | age <br>----+-------+-------+-------+-----<br> 2 | Davis | | Evans | 22<br>(1 row) |
NULL is also perfect for data that simply doesn’t apply to certain records. For example, a divorce status for child records:
|
1 |
postgres=# CREATE TABLE person(id INTEGER, name TEXT, type TEXT, divorced bool);<br>postgres=# SELECT * FROM person;<br> id | name | type | divorced <br>----+-------+-------+---------<br> 1 | Alice | WOMAN | f<br> 3 | Davis | KID | <br> 2 | Bob | MAN | t<br>(3 rows) |
This distinction becomes critical when dealing with test scores or measurements:
|
1 |
postgres=# SELECT * FROM students_mark;<br> id | name | marks <br>----+-------+-------<br> 1 | Alex | 90<br> 2 | Bob | 0<br> 2 | Davis | <br>(3 rows)<br> |
|
1 |
postgres=# SELECT * FROM students_mark WHERE marks IS NULL;<br> id | name | marks <br>----+-------+-------<br> 2 | Davis | <br>(1 row) |
|
1 |
postgres=# SELECT * FROM students_mark WHERE marks = 0;<br> id | name | marks <br>----+------+-------<br> 2 | Bob | 0<br>(1 row) |
Here, Bob scored zero (he took the test but failed), while Davis has NULL (perhaps he was absent and hasn’t taken the test yet). This distinction matters for your business logic.
NULL isn’t mysterious once you understand its purpose, but it does require a different mindset. In C, it’s 0; in Java, it’s a non-reference; in PostgreSQL, it represents a third logical state meaning “unknown” or “not applicable.”
The key is treating NULL with the respect it deserves. When you need to work with potentially NULL values, reach for IS NULL, COALESCE, and NULLIF. Try to use regular equality operators or mathematical operations, and you’ll end up with misleading or empty results that will have you scratching your head.
Mastering NULL handling is just one piece of building reliable PostgreSQL applications. As your schema grows and your workload scales, you’ll face new challenges around indexing, high availability, observability, and performance optimization.
That’s where having the right resources makes all the difference. We’ve assembled comprehensive tools, tutorials, and open source expertise to help you keep PostgreSQL reliable, consistent, and future-ready without expensive proprietary add-ons or vendor lock-in.
Check out our PostgreSQL Resource Center to discover how Percona’s fully open source solution gives you the freedom to deploy anywhere, reduce costs, and maintain complete control over your database infrastructure.
Resources
RELATED POSTS
