# How to add a NOT NULL column safely in PostgreSQL

> **Quick answer:** If you can give the column a **constant default**, just
> `ADD COLUMN ... NOT NULL DEFAULT <const>`.  It is metadata-only and instant since
> PostgreSQL 11.  If you need a **backfilled** value with no fixed default, do it in
> steps: add the column nullable and backfill in batches, then, on
> **PostgreSQL 18+**.`ADD CONSTRAINT ... NOT NULL <col> NOT VALID` (instant) and
> `VALIDATE CONSTRAINT` (scans under `SHARE UPDATE EXCLUSIVE`, so reads *and*
> writes continue).  On **PostgreSQL 17 and earlier** you need the longer sequence:
> a validated `CHECK (col IS NOT NULL)`, then `SET NOT NULL` (which PostgreSQL 12+
> does without re-scanning the table), then drop the check.

:::note[Version-sensitive]
The fast paths here depend on your major version: constant-default `ADD COLUMN` is
metadata-only from **PostgreSQL 11**, and the no-scan `SET NOT NULL` is
**PostgreSQL 12+**. From **PostgreSQL 18**, not-null constraints are stored in
`pg_constraint` like any other constraint, so you can `ADD CONSTRAINT ... NOT NULL
<col> NOT VALID` and `VALIDATE CONSTRAINT` later, which removes the
`ACCESS EXCLUSIVE` scan from the picture entirely.  Confirm against the version you
run.  This aligns with
[which ALTER TABLE statements lock a table](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-table/).
:::

## The easy case: a constant default

If every existing row can share the same value, one statement does it:

```sql
ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'active';
```

Since **PostgreSQL 11**, this is **metadata-only**: the default is stored in the
catalog and applied to existing rows logically, so there is no table rewrite even
though the column is `NOT NULL`. It takes an `ACCESS EXCLUSIVE` lock only for the
instant it updates the catalog.

(On PostgreSQL 10 and earlier, this same statement rewrote the entire table under
that lock, the classic migration-that-took-the-site-down.)

## The careful case: NOT NULL with a backfill

When there is no single default, the value must be computed per row, or comes
from elsewhere, do not do it in one shot.  Do it in steps: add the column, backfill
it, and then attach the constraint.  On **PostgreSQL 18+** attaching the constraint
is two commands and never scans under an exclusive lock; on **17 and earlier** it is
a longer sequence built around a placeholder `CHECK`.

**1.  Add the column nullable (instant):**

```sql
ALTER TABLE orders ADD COLUMN region text;
```

**2.  Backfill in batches** so no single statement runs as one long transaction
and floods the table with dead tuples. (An `UPDATE` takes only a `ROW EXCLUSIVE`
lock, it never blocks readers, so the hazard here is transaction length and
bloat, not locking):

```sql
UPDATE orders SET region = compute_region(id)
WHERE region IS NULL AND id BETWEEN 1 AND 10000;
-- repeat in ranges until done
```

Batching keeps each transaction short and lets autovacuum keep up (a single
table-wide `UPDATE` is a
[bloat](https://www.dbgorilla.com/learn/postgres/what-is-table-bloat-in-postgresql-and-how-to-fix-it/)
hazard).

**3.  Add the NOT NULL constraint `NOT VALID`**, on **PostgreSQL 18+**, not-null
constraints live in `pg_constraint` like any other constraint, so you can add one
unvalidated.  No placeholder `CHECK` is needed:

{/* sql-check: pg 18+ */}
```sql
ALTER TABLE orders ADD CONSTRAINT orders_region_not_null
  NOT NULL region NOT VALID;
```

This takes an `ACCESS EXCLUSIVE` lock only for the instant it updates the catalog.  There is no scan.  The column is enforced against **new** inserts and updates
immediately (`pg_attribute.attnotnull` flips to true right away); only
pre-existing rows go unchecked, and the constraint is marked
`pg_constraint.convalidated = false` until you validate it.

**4.  Validate it**.  This is the step that scans, and it runs under
`SHARE UPDATE EXCLUSIVE`, which allows reads **and** writes for the whole scan:

{/* sql-check: pg 18+ */}
```sql
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;
```

That is it.  There is no `SET NOT NULL` step and nothing to drop afterwards.

**On PostgreSQL 17 and earlier**, not-null is only a column attribute, so you need
the older four-step sequence: add a placeholder `CHECK (region IS NOT NULL)
NOT VALID`, `VALIDATE` it under `SHARE UPDATE EXCLUSIVE`, then `SET NOT NULL`, which on **PostgreSQL 12+** skips the table scan because it trusts the validated
`CHECK`, but is *not* lock-free: it still takes an `ACCESS EXCLUSIVE` lock, so run
it behind a `lock_timeout` and retry rather than letting it queue behind a long
query and stall all traffic.  Then drop the now-redundant check:

{/* sql-check: isolated */}
```sql
ALTER TABLE orders ADD CONSTRAINT orders_region_check
  CHECK (region IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_check;
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_region_check;  -- now redundant
```

On PostgreSQL 11 and earlier, `SET NOT NULL` still does a full-table verification
scan under `ACCESS EXCLUSIVE`, so guard it with a `lock_timeout` and run it in a
low-traffic window.

## What to avoid

- **A volatile default.** `ADD COLUMN ... DEFAULT random()` (or any volatile
  function) must differ per row, so it **rewrites the whole table**. The
  metadata-only path is only for constant defaults.
- **One giant backfill UPDATE.** Not a locking problem (`UPDATE` is only
  `ROW EXCLUSIVE`).  It is one very long transaction plus a huge dead-tuple burst
  that autovacuum cannot clean until it commits.  Batch it.

## Why does aI-generated migration code get this wrong?

Your AI coding agent (Claude Code, Cursor) writes the obvious statement.`ADD COLUMN ... NOT NULL` with a per-row backfill in a single `UPDATE`, or a
volatile default, because it is the direct translation of the request.  It cannot
see that `orders` has 200 million rows, or which PostgreSQL version you run
(which decides whether the fast paths even apply).  Whether a migration is instant
or an outage is a function of data volume and version, not the SQL text.

## How do I keep migrations safe?

- Prefer the constant-default one-liner when you can (PG 11+).
- Otherwise on **PG 18+**: nullable → batch backfill → `NOT NULL ... NOT VALID` →
  `VALIDATE CONSTRAINT`.
- On **PG 17 and earlier**: nullable → batch backfill → validated CHECK →
  `SET NOT NULL` → drop the check.
- Guard every `ACCESS EXCLUSIVE` step with a short `lock_timeout`.

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor)
the context a safe migration needs, the table's row count and size, and the
server version that determines which fast path applies, so it can recommend the
constant-default one-liner where it is safe, or the nullable-backfill-validate
sequence where it is not.  It surfaces and explains; it does not run your
migrations. [Get started free →](https://app.dbgorilla.com/signup)

## FAQ

**Is adding a NOT NULL column with a default slow?**
Not since PostgreSQL 11, a constant default is metadata-only, no rewrite even
with `NOT NULL`. On 10 and earlier it rewrote the table.

**How do I add a NOT NULL column without a fixed default?**
Add it nullable and backfill in batches.  On **PostgreSQL 18+** that is followed by
just two commands: `ADD CONSTRAINT ... NOT NULL <col> NOT VALID`, an instant
catalog change that immediately enforces the constraint on new inserts and
updates, then `VALIDATE CONSTRAINT`, which scans the pre-existing rows under
`SHARE UPDATE EXCLUSIVE` so reads and writes both continue.  No `SET NOT NULL`, and
nothing to drop afterwards.  On **PostgreSQL 17 and earlier**, add a validated
`CHECK (col IS NOT NULL)`, then `SET NOT NULL`, on 12+ the validated check lets
it skip the scan, but it still takes a brief `ACCESS EXCLUSIVE` lock, so use a
`lock_timeout`, then drop the check.

**Why backfill in batches?**
Not for locking, an `UPDATE` takes only `ROW EXCLUSIVE` and does not block
readers.  A single table-wide `UPDATE` is one long transaction with a dead-tuple
burst that autovacuum cannot reclaim while it is open, so the table bloats.  Batches
keep transactions short and let vacuum keep up.

**Does a volatile default rewrite the table?**
Yes, the metadata-only path is only for constant defaults.  A volatile default
like `random()` rewrites every row under `ACCESS EXCLUSIVE`.

## Related

- [Which ALTER TABLE statements lock a PostgreSQL table?](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-table/)
- [What is table bloat in PostgreSQL and how do I fix it?](https://www.dbgorilla.com/learn/postgres/what-is-table-bloat-in-postgresql-and-how-to-fix-it/)
- [How to add a PostgreSQL index without downtime](https://www.dbgorilla.com/learn/postgres/how-to-add-a-postgres-index-without-downtime/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)