Handling NULLs in PostgreSQL (For Beginners):

Introduction

Have you ever wondered what are null values in data? Honestly, before diving deep, I assumed they were equal to zero, if I encountered them in numeric columns and if in text columns, simply nothing. As a result, I ended up with mismatched numbers, wrong visualizations. My worst nightmare! I got baffled, but I was sure that this must do something with these null values and I decided to learn more about them and their behavior so I can be confident that the report I generated is absolutely flawless! If this resonates with you, come, let’s walk through them!

What is NULL?

• NULL means absence of data

• We cannot replace NULL with constants like 0 in all the scenarios.

• NULL is not equal to 0, not equal to blank space, not equal to empty string in fact null is not equal to null also! Surprising, right?

NULL is a special value in the data world which needs to be handled explicitly.

Example of NULL:

• Number of iphone17 users in bootcamp = NULL

  Number of iphone17 users in bootcamp = 0

• When I read the statements above, then only I could understand what null means! It means ABSENCE OF DATA. First scenario means we don’t know how many iphone users are there, we cannot say they are zero, right?

Businesses allow them to exist in their data because they have a meaning in their context.

Example: middle name being optional in many online forms we fill.

This is all about understanding nulls. Let’s see how to handle them.

My first approach in handling nulls is at the design level itself – Pass NOT NULL constraint to keep the code clean. Only allow null value if it has a meaning.

Functions in PostgreSQL to handle NULLS:

• ISNULL, IS NOT NULL

• CASE

• NULLIF

• COALESCE

We should use IS NULL and IS NOT NULL to filter null values from our data.

Why can’t we use = with NULL?

A common mistake beginners make is trying to compare NULL using the equals operator:

SELECT *

FROM employees

WHERE manager_id = NULL; -- ❌ Incorrect

This query returns no rows, even if NULL values exist.

👉 Reason:

NULL represents an unknown value, so it cannot be compared using = or !=.

Using IS NULL

To find rows where a column has missing (NULL) values:

SELECT *

FROM employees

WHERE manager_id IS NULL;

This returns employees who do not have a manager assigned.

👉 Simple takeaway:

Use IS NULL when you want to find missing or unknown data.

Using IS NOT NULL

To find rows where a column has actual values (not NULL):

SELECT *

FROM employees

WHERE manager_id IS NOT NULL;

This returns employees who have a manager assigned.

👉 Simple takeaway:

Use IS NOT NULL when you want only rows with valid data.

Using CASE

The CASE statement in PostgreSQL is like an “if…then…else” for SQL.

• It allows you to return different results based on conditions.

• Very useful for categorizing data, handling NULLs, or calculating new columns.

Now let’s see how to USE CASE to handle null values.

SELECT student,

CASE

WHEN phone_number IS NULL THEN 'No Phone'

ELSE phone_number

END AS contact

FROM students;

Result:

student contact

Alex 1234567890

Maria No Phone

Samy No Phone

Using NULLIF

NULLIF is a function that compares two values.

• If the values are equal, it returns NULL.

• If they are different, it returns the first value.

Imagine a column where 0 means “no data”, but you want to treat it as missing:

product discount

A 10

B 0

👉 Convert 0 into NULL:

SELECT product, NULLIF(discount, 0) AS discount

FROM products;

Now:

• 10 stays 10

• 0 becomes NULL

Result:

product discount

A 10

B NULL

NULLIF helps you turn unwanted values (like 0 or duplicates) into NULL, so your queries don’t break or give wrong results.

Using COALESCE

COALESCE is a function that returns the first non-NULL value from a list of values.

• It’s useful for replacing NULLs with a default value.

• Works with numbers, text, dates, or any data type.

Example:

employee salary

Alex 50,000

Mary NULL

SELECT employee, coalesce(salary, 0 ) AS salary

FROM employees;

Result:

employee salary

Alex 50,000

Mary 0

COALESCE helps to replace missing data in SELECT queries and prevent NULLs in calculations and reports.

Now that we have seen how to handle NULLs in PostgreSQL, let me give a brief introduction of NULL Traps!

What Are NULL Traps? (Brief Introduction)

While handling NULL values using COALESCE, NULLIF, and CASE is important, there is another common challenge called a NULL trap.

A NULL trap occurs when queries—especially joins or filters—produce unexpected results because of NULL values. For example, rows may disappear from results without any obvious error.

We will explore NULL traps in detail, along with real-world examples and solutions, in my next blog.

Conclusion

Handling NULL values correctly is essential for writing reliable PostgreSQL queries. Using functions like IS NULL, COALESCE, NULLIF, and CASE helps us manage missing data effectively and avoid common mistakes.

However, NULL-related issues don’t stop here. In the next blog, we’ll dive deeper into NULL traps and how they can silently impact our query results.