NULL and DEFAULT Values

Objectives

By the end of this lesson, you should be able to:

• Explain what NULL represents
• Use DEFAULT to fill in a value automatically
• Explain why NULL needs special handling in comparisons

💡 Why this matters: Not every piece of data is known at the time a row is created. NULL is how a database represents “no value,” and DEFAULT is how it avoids asking for one every single time.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

What NULL Means

NULL represents the absence of a value, not zero, not an empty string, genuinely unknown or not applicable. A department column left out of an INSERT stores NULL unless told otherwise:

INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Maya', 'Kapoor', 'maya.kapoor@example.com', 55000);

 first_name | department
------------+------------
 Maya       |

Maya’s department is NULL, she genuinely doesn’t have one recorded yet, that’s different from her department being an empty string '', which would mean something else entirely, that it’s known and specifically blank.

Filtering for NULL

NULL can’t be compared with =, WHERE department = NULL never matches anything, even for rows where department actually is NULL. A dedicated check is required (covered fully in Module 6):

SELECT first_name, department FROM employees WHERE department IS NULL;

 first_name | department
------------+------------
 Maya       |

IS NULL (and IS NOT NULL) are the only correct ways to check for NULL, this quirk is exactly why they exist as their own syntax instead of just using =.

DEFAULT: Fill This In Automatically

hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
is_active BOOLEAN NOT NULL DEFAULT true

If an INSERT doesn’t mention a column with a DEFAULT, PostgreSQL fills in that default value instead of requiring it every time, or leaving it NULL:

INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Erin', 'Walsh', 'erin.walsh@example.com', 78000);

 first_name | hire_date  | is_active
------------+------------+-----------
 Erin       | 2026-07-28 | t

Neither hire_date nor is_active was mentioned in the INSERT, but both were filled in automatically, hire_date with today’s date (CURRENT_DATE), is_active with true. A DEFAULT can be a literal value, or a function call like CURRENT_DATE or now(), evaluated fresh every time a row is inserted.

Try It

1. Insert a row into a table with a nullable notes column, without mentioning notes at all, then confirm it stored as NULL, not an empty string.
2. Write a query that finds every employee row where department IS NULL.
3. Add a DEFAULT 0 to a bonus column, then insert a row without mentioning bonus, and confirm it stored as 0, not NULL.

Recap

• NULL represents an unknown or missing value, distinct from zero or an empty string.
• WHERE column = NULL never matches, IS NULL and IS NOT NULL are the correct checks.
• DEFAULT fills in a value automatically when an INSERT doesn’t specify one, a literal or a function call like CURRENT_DATE.

Next lesson: PRIMARY KEY and NOT NULL, the constraints that make a row identifiable and complete.