The NULL Trap in PostgreSQL

The NULL Trap in PostgreSQL

One of the biggest traps for junior (and even senior) developers in the database world is the infamous NULL. Most of us imagine NULL as a zero 0 or an empty string "". But in PostgreSQL, NULL is a huge I DON’T KNOW.

Let’s solve this mystery with a simple Grocery Store scenario, hands-on style. Open your terminal, and let’s get dirty

The Lab Setup: Creating Our Grocery Store

Open your psql terminal or any SQL editor and run these queries to see the phantom data for yourself.

First, let’s create a temporary table:

CREATE TEMP TABLE manav (
    urun_adi TEXT,
    stok_adedi INTEGER
);


INSERT INTO manav (urun_adi, stok_adedi) VALUES 
('Apple', 10),           -- We have apples (Known value)
('Pear', 0),            -- Out of pears (Zero, but still a known value)
('Dragon Fruit', NULL); -- Stock not entered yet (Unknown value!)

Experiment 1: The Great Disappearing Act

Now, let’s ask a simple question: Show me the products where stock is NOT 10.

Logically, you’d expect Pear (0) and Dragon Fruit (NULL) to show up, right? Let’s try:

SELECT * FROM manav WHERE stok_adedi != 10;

The Result: Only Pear shows up. Wait, where is the Dragon Fruit?

The Technical Reason: When Postgres sees NULL != 10, it doesn't say Yes or No. It says I don't know! Since Postgres only returns rows where the condition is strictly TRUE, the Dragon Fruit evaporates from your results.

Experiment 2: The Equality Trap

Common mistake: trying to find the missing stock using the standard equals sign.

-- ATTENTION: This query returns NOTHING!
SELECT * FROM manav WHERE stok_adedi = NULL;

The Rule: In SQL, nothing is equal to NULL—not even NULL itself! Never use = with it.

The Right Way:

SELECT * FROM manav WHERE stok_adedi IS NULL;

Boom! The Dragon Fruit is back from the shadows.

The Ultimate Fix: COALESCE

If you don’t want your queries to break because of unknown values, you need to turn that mystery into a real number. Meet the COALESCE function.

It basically says: If this value is NULL, use this default value instead.

-- Treat NULLs as 0 and then compare
SELECT urun_adi, COALESCE(stok_adedi, 0) as stock_status
FROM manav 
WHERE COALESCE(stok_adedi, 0) != 10;

What happened here?

• Postgres checked Dragon Fruit: Is stock NULL? Yes. Okay, treat it as 0.
• Then it compared: Is 0 different from 10? Yes
• Result: Dragon Fruit is successfully listed.

Key Takeaways for Your Next Migration

• NULL != 0: Zero is a number; NULL is a state of unknown.
• Stop writing = NULL: Use IS NULL or IS NOT NULL instead.
• Safe Queries: If a column can be NULL, always use COALESCE to provide a fallback value.

Getting rid of phantoms in your database is that simple