DataslopeNOT NULL and Default Values
Learn how required columns, optional columns, NULL, and defaults shape what a row is allowed to omit.
When a new customer signs up, can their email be missing? Can their nickname be missing? Those two questions may look similar, but they are design decisions with very different consequences.
NULL, NOT NULL, and DEFAULT are how the schema answers them.
NULL means unknown or absent
In SQL, NULL means "there is no value here." It might mean the value is unknown, not applicable, not collected yet, or deliberately absent. The important part is that NULL is not the same as an empty string, zero, or false.
Allowing NULL is a real design choice. It says, "this fact is optional
for this kind of row." Sometimes that is exactly right. A customer may
not have a middle_name. A shipped order may have a delivered_at,
while a new order does not yet.
But if the value is essential to what the row is, allowing NULL makes invalid rows possible.
Required vs. optional columns
A NOT NULL constraint says every row must provide a value for that column. PostgreSQL rejects any insert or update that would leave the column empty.
Use NOT NULL for values the row cannot make sense without: account
email, product name, order date, line-item quantity. Allow NULL for
values that are genuinely optional or not known yet.
Optional is not sloppy
Allowing NULL is not automatically bad. The design mistake is allowing NULL when the business rule says the value is required.
DEFAULT fills in an omitted value
A DEFAULT is the value PostgreSQL uses when an insert leaves a column out. Defaults are useful when there is a safe, honest value the schema can choose.
Examples:
created_at TIMESTAMPTZ DEFAULT now()status TEXT DEFAULT 'draft'is_active BOOLEAN DEFAULT true
A default is not the same as NOT NULL. A column can have a default and
still allow someone to explicitly insert NULL unless you also declare
NOT NULL.
Quick check
What does NULL mean in SQL?
The number zero.
An empty string.
A missing, unknown, or absent value.
The word "null" stored as text.
Watch NOT NULL and DEFAULT work
This table requires email, but it supplies a default account status.
The first insert works and receives the default. The second insert is
rejected because the required email is missing.
The schema is doing two jobs: it refuses a row without its required identity information, and it chooses a safe default status when the writer does not specify one.
The beginner NULL comparison gotcha
NULL has one surprising behavior: comparisons with NULL do not return
ordinary true or false. If the value is unknown, then email = NULL is
also unknown.
Use IS NULL and IS NOT NULL when you want to test for missing data.
This is called three-valued logic: SQL conditions can be true,
false, or unknown. You do not need the formal theory yet. Just remember
that NULL is special, and ordinary = comparisons are not how you find
it.
Required data is part of meaning
Think of NOT NULL as a promise: every row has this fact. That promise
makes the table easier to trust and easier to query.
If every order has ordered_at NOT NULL, reports do not need to wonder
whether some orders have no date. If every product has name NOT NULL,
interfaces can show product names without inventing placeholders.
Defaults make a different promise: when the writer omits the value, the database will use a standard one.
Quick check
A column has status TEXT DEFAULT 'draft' but does not have NOT NULL. What can happen?
The column can never contain NULL.
Omitted status values become 'draft', but an explicit NULL may still be stored.
PostgreSQL rejects every insert that omits status.
The default value must be unique.
Check your understanding
When should a column usually be declared NOT NULL?
Whenever the column stores text.
Whenever the value might be unknown at first.
When every valid row must have that value for the row to make sense.
Whenever you want the column to sort faster.
What is the role of a DEFAULT value?
To reject duplicate values.
To supply a value when an insert omits the column.
To make a column optional in every query.
To turn NULL into zero in comparisons.
Which condition correctly finds rows where nickname is missing?
nickname = NULL
nickname = ''
nickname IS NULL
nickname DEFAULT NULL