21 NOT NULL and DEFAULT

Require values with NOT NULL and supply useful defaults for new rows.

NOT NULL and DEFAULT are the two most useful per-column rules. NOT NULL says the column always has a value — every INSERT and UPDATE must provide one (or rely on a default). DEFAULT expr provides the value when the column is omitted. Together, they make schemas easier to reason about: you never have to write IF column IS NULL in application code if the column is NOT NULL.

What you’ll learn

Enforcing required values with NOT NULL
Supplying defaults with DEFAULT expr
Volatile vs constant defaults and what that means at scale
Combining NOT NULL and DEFAULT for “always has a value”
Removing or changing defaults later

Example

Example 21.1 NOT NULL and DEFAULT

DROP TABLE IF EXISTS tasks_example;

CREATE TABLE tasks_example (
  id          integer PRIMARY KEY,
  title       text NOT NULL,
  done        boolean NOT NULL DEFAULT false,
  priority    integer NOT NULL DEFAULT 3 CHECK (priority BETWEEN 1 AND 5),
  due_at      timestamptz,                              -- nullable
  created_at  timestamptz NOT NULL DEFAULT current_timestamp
);

-- Defaults fill in done, priority, created_at
INSERT INTO tasks_example (id, title) VALUES (1, 'Learn SQL');

-- Explicit values override the defaults
INSERT INTO tasks_example (id, title, done, priority, due_at)
VALUES (2, 'Ship lesson', true, 1, current_timestamp + interval '1 day');

SELECT * FROM tasks_example ORDER BY id;

-- Remove a default later (or change it)
ALTER TABLE tasks_example ALTER COLUMN priority SET DEFAULT 1;
ALTER TABLE tasks_example ALTER COLUMN priority DROP DEFAULT;
ALTER TABLE tasks_example ALTER COLUMN priority SET DEFAULT 3;

-- Adding NOT NULL to a column requires no NULLs to exist
-- ALTER TABLE tasks_example ALTER COLUMN due_at SET NOT NULL;  -- would fail if any due_at is NULL

NOT NULL is one of the cheapest, highest-impact constraints you can add — it eliminates an entire class of bugs (forgotten null checks, broken aggregates, surprising joins). Mark columns NOT NULL whenever the application logic guarantees a value.

DEFAULT is evaluated at insert time, not at table creation time. DEFAULT current_timestamp runs current_timestamp once per inserted row, returning the transaction start time. DEFAULT gen_random_uuid() generates a fresh UUID for each row.

Defaults that are constants (DEFAULT 0, DEFAULT 'pending') are fast to add to existing tables — PostgreSQL 11+ stores the constant default in metadata without rewriting the table. Nonvolatile defaults, including the STABLE expression current_timestamp, can also use the fast path. Volatile defaults such as random() and clock_timestamp() require a value for each existing row and rewrite the table.

Run the example

To run:

$ psql -f source/not-null-and-default.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
 id |    title    | done | priority | due_at | created_at
----+-------------+------+----------+--------+-------------
  1 | Learn SQL   | f    |        3 |        | 2024-xx-xx
  2 | Ship lesson | t    |        1 | 2024-xx-xx | 2024-xx-xx
(2 rows)
 ...

Common pitfalls

Treating '' (empty string) as “missing” — pick one and be consistent. Most teams use NULL for “no value” and reserve '' for “intentionally blank.” Mixing the two leads to half-broken queries forever.
Defaults are not automatically reapplied on UPDATE. To “reset to default,” use UPDATE t SET col = DEFAULT WHERE ....
Adding NOT NULL to an existing column that may have NULLs fails. Backfill the column first, then SET NOT NULL.
INSERT INTO t DEFAULT VALUES is your friend when every column has a default — it lets the database fill in everything.

Tip

A common pattern is created_at timestamptz NOT NULL DEFAULT current_timestamp paired with updated_at timestamptz NOT NULL DEFAULT current_timestamp. The created_at is set on INSERT and never changes; an updated_at needs a trigger (covered later) to bump on every UPDATE.

Try it

Try INSERT INTO tasks_example (id) VALUES (3); — the NOT NULL on title should reject it. Then INSERT INTO tasks_example (id, title, priority) VALUES (3, 'X', 9); to see the CHECK reject an out-of-range priority.

Downloads

SQL file: not-null-and-default.sql · All lesson files · Setup guide