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.
What is NULL?
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.
How NULL works in different languages
To really grasp PostgreSQL’s approach, let’s see how other languages handle null values.
C/C++
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 2 3 4 5 6 7 8 9 10 11 12 13 |
#if defined (_STDDEF_H) || defined (__need_NULL) #undef NULL /* in case <stdio.h> has defined it. */ #ifdef __GNUG__ #define NULL __null #else /* G++ */ #ifndef __cplusplus #define NULL ((void *)0) #else /* C++ */ #define NULL 0 #endif /* C++ */ #endif /* G++ */ #endif /* NULL not defined and <stddef.h> or need NULL. */ #undef __need_NULL |
A quick test program shows this in action:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
#include <stddef.h> #include <stdio.h> void main() { if ('0' == NULL) printf("NULL is '0' n"); if ("" == NULL) printf("NULL is empty string n"); if (' ' == NULL) printf("NULL is space n"); if (0 == NULL) printf("NULL is 0 n"); } |
The output confirms that in C, NULL is simply 0.
Java
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 2 3 4 5 6 7 8 |
public class Test { public static void main (String[] args) throws java.lang.Exception { System.out.println("Null is: " + null); } } Null is: null |
Understanding NULL in PostgreSQL
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 2 3 4 5 |
postgres=# SELECT 1 = 1 result; result -------- t (1 row) |
Normal text comparison:
|
1 2 3 4 5 |
postgres=# SELECT 'foo' = 'foo' result; result -------- t (1 row) |
Compare NULL with NULL:
|
1 2 3 4 5 6 |
postgres=# SELECT NULL = NULL result; result -------- (1 row) |
Inequality doesn’t help:
|
1 2 3 4 5 6 |
postgres=# SELECT NULL != NULL result; result -------- (1 row) |
Math operations are equally fruitless:
|
1 2 3 4 5 |
postgres=# SELECT NULL * 10 is NULL result; result -------- t (1 row) |
How to use NULL in PostgreSQL
Since ordinary operators don’t work with NULL, PostgreSQL provides specialized tools designed specifically for these situations.
IS NULL / IS NOT NULL
|
1 2 3 4 5 |
postgres=# SELECT NULL is NULL result; result -------- t (1 row) |
|
1 2 3 4 5 |
postgres=# SELECT NULL is NOT NULL result; result -------- f (1 row) |
COALESCE
COALESCE returns the first non-NULL argument from a list. This is incredibly useful for providing fallback values:
|
1 |
COALESCE (NULL, 2 , 1); |
NULLIF
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 2 3 4 5 6 7 8 9 10 11 12 |
postgres=# SELECT NULLIF (10, 10); nullif -------- (1 row) postgres=# SELECT NULLIF (10, 100); nullif -------- 10 (1 row) |
Examples of different NULL uses
Let’s look at some real-world scenarios where NULL handling becomes crucial.
Example 1: Optional middle names
Consider a student table where middle names are optional:
|
1 2 3 4 5 6 7 8 9 |
postgres=# CREATE TABLE student(id INTEGER, fname TEXT, sname TEXT, lname TEXT, age INTEGER); postgres=# SELECT * FROM STUDENT; id | fname | sname | lname | age ----+-------+-------+-------+----- 1 | Adams | Baker | Clark | 21 2 | Davis | | Evans | 22 3 | Ghosh | Hills | | 24 (3 rows) |
If you try searching for empty strings, you’ll miss the NULL middle name:
|
1 2 3 4 |
postgres=# SELECT * FROM STUDENT WHERE sname = ''; id | fname | sname | lname | age ----+-------+-------+-------+----- (0 rows) |
Instead, use IS NULL to find records with missing middle names:
|
1 2 3 4 5 |
postgres=# SELECT * FROM STUDENT WHERE sname IS NULL; id | fname | sname | lname | age ----+-------+-------+-------+----- 2 | Davis | | Evans | 22 (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 2 3 4 5 6 7 8 |
postgres=# CREATE TABLE person(id INTEGER, name TEXT, type TEXT, divorced bool); postgres=# SELECT * FROM person; id | name | type | divorced ----+-------+-------+--------- 1 | Alice | WOMAN | f 3 | Davis | KID | 2 | Bob | MAN | t (3 rows) |
Example 2: Distinguishing “zero” from “unknown”
This distinction becomes critical when dealing with test scores or measurements:
|
1 2 3 4 5 6 7 |
postgres=# SELECT * FROM students_mark; id | name | marks ----+-------+------- 1 | Alex | 90 2 | Bob | 0 2 | Davis | (3 rows) |
|
1 2 3 4 5 |
postgres=# SELECT * FROM students_mark WHERE marks IS NULL; id | name | marks ----+-------+------- 2 | Davis | (1 row) |
|
1 2 3 4 5 |
postgres=# SELECT * FROM students_mark WHERE marks = 0; id | name | marks ----+------+------- 2 | Bob | 0 (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.
Conclusion
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.
About the Author
Ibrar AhmedJoined Percona in the month of July 2018. Before joining Percona, Ibrar worked as a Senior Database Architect at EnterpriseDB for 10 Years. Ibrar has 18 years of software development experience. Ibrar authored multiple books on PostgreSQL.
> Especially in databases (PostgreSQL), NULL has some different concepts, so be careful when writing queries involving NULL.
In what databases?
NULL meaning is dictated by SQL standard, and Postgres follows it.
It would be interesting to see if some databases treat it differently compared to the standard.
PostgreSQL is “following” the SQL standard, but NULL is definitely different in other Language like C and Java. This is specifically for those who have programming background and try to write SQL queries.
Good article, but I dissent on one part: I believe that SQL NULL meaning is unclear, and depends on the context.
I explained my point in an article last year:
https://federico-razzoli.com/what-does-null-mean-in-sql
To sum up, my view is that some earlier versions of relational algebra defined two markers (I-marker and A-marker) that defined, respectively, an absent value and an unknown value. SQL standard merged these idea into a single NULL marker in an inconsistent way. The result is that, in practice, sometimes NULLs semantics only make sense if it means absent, sometimes they make sense if it means unknown, and sometimes they don’t make sense in both cases.
@Federico Razzoli, agreed, I just explained very basic difference of NULL in normal programming language and SQL.
@Ibrar Ahmed, on that part I agree. I find most of your article good and useful, and I’m sorry for not stressing this in my first comment. It’s good to explain developers that SQL NULL is not what they could expect if they are familiar with C or Java.
The sentence I disagree with is “In PostgreSQL, NULL means no value”. I agree that this is PostgreSQL intention (because this is what SQL standard says), but in practice all DBMSs I know treat it too inconsistently.
Every so often a cringy article like this comes out from someone who just found the word NULL in a few different places and went about trying to determine why the same word seemingly means different things.
First, you’re comparing an RDBMS engine (PostgreSQL) to programming languages (C & Java). That doesn’t even make sense. These are not the same thing.
Second, you need to understand it in the historical context. In C, NULL is 0. Why? In Unix systems, the first page of memory (which includes address 0) is typically set as inaccessible to a process. When pointers are initialized to 0, they point to this inaccessible piece of memory. This is one of the best ways to catch an uninitialized pointer, as using it will result in your program crashing immediately. So the uninitialized pointer is a null pointer. We don’t use NULL in other contexts – we just use 0. I wouldn’t put “for (x=NULL; …”. There’s a reason we have this, and it’s not to make typing “0” more difficult.
Third, your database examples are surprisingly good given the rest of this, but you missed one of the qualities of NULL values that is useful. They aren’t used in aggregate functions. For instance, taking the students_mark example, “select count(marks) from students_mark” will give you “2”, and “select average(marks) from students_mark” will give you “4.5”. In a database context, NULL means “missing or unknown”. Unfortunately, those are different concepts and you have to use your programming to make up for it.
Please also see the remarks from Federico Razzoli.
I would also add info about “null” in javascript.
@Michael Rikmas I don’t disagree with you, but I just wrote this article for the people who has some programming experience in some language and try to write some SQL queries. I am comparing NULL in C/Java and SQL because name NULL is same but concept is 100% different. I met many people who does not know the basic concept of NULL in SQL. Secondly I really don’t want to go in very detail in user level blog, hope you understand.
@Ibrar I guess the article is targeting a specific audience that seem to have a niche gap in their knowledge. When looked at from that point of view, a decent write up with good relevant examples.
Also, Federico’s article goes nicely with it. Thanks for the reminder @Federico.
Yes, this is for some specified users who comes from different languages and try to learn SQL’s NULL.
NULL is not always good
Yes, we should hope to have data in all column 🙂
Nice article. It’s also interesting to note that NULL values are not regarded in unique indices. So, while a duplicate non-NULL value is not allowed in a column that has a unique index on it, more than one NULL value *is* allowed. I’m not sure if this behavior is consistent across all major DMBSs but I suspect so.