# NOT NULL and DEFAULT

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

---

LLMS index: [llms.txt](/en/llms.txt)

---

`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 {#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}

```sql {num="21.1" caption="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 {#run}

To run:

```text
$ 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 {#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] 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`.

> [!NOTE] 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 {#downloads}

SQL file: [not-null-and-default.sql](/examples/source/not-null-and-default.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)