---
title: "Make PostgreSQL SET NOT NULL skip the table scan | Proofline"
description: "Making a column required with ALTER COLUMN ... SET NOT NULL reads the whole table while it holds a lock that blocks reads and writes."
url: https://proofline.sh/learn/postgres-set-not-null-scan/
---

[Learning center](/learn/)

# Make PostgreSQL SET NOT NULL skip the table scan

Making a column required with ALTER COLUMN ... SET NOT NULL reads the whole table while it holds a lock that blocks reads and writes. On PostgreSQL 12 and later it skips that read when a validated CHECK (column IS NOT NULL) constraint already proves the column has no nulls. Validate that constraint first, under a weaker lock, and the SET NOT NULL itself becomes a catalog change.

Breaking migrations The Proofline team November 20, 2026 3 min read

## Watch PostgreSQL scan the table

A payments table needs `currency` to be required. Two million rows hold a value, and one row written by an old import does not. We ran this on PostgreSQL 16.15 with `client_min_messages` set to `debug1`, which makes PostgreSQL say when it verifies a table.

```
CREATE TABLE payments (id integer PRIMARY KEY, currency text);
INSERT INTO payments SELECT g, 'EUR' FROM generate_series(1, 2000000) g;
UPDATE payments SET currency = NULL WHERE id = 1999999;

SET client_min_messages = debug1;
\timing on
ALTER TABLE payments ALTER COLUMN currency SET NOT NULL;
```

```
DEBUG:  verifying table "payments"
ERROR:  column "currency" of relation "payments" contains null values
Time: 104.780 ms
```

After fixing the row, the same statement succeeds after another full scan:

```
UPDATE payments SET currency = 'EUR' WHERE id = 1999999;
ALTER TABLE payments ALTER COLUMN currency SET NOT NULL;
```

```
DEBUG:  verifying table "payments"
ALTER TABLE
Time: 74.766 ms
```

Both attempts held an `ACCESS EXCLUSIVE` lock for the length of the scan. At two million narrow rows that is about a tenth of a second. The time grows with the table, and every query on it waits.

## Prove it with a constraint first

Remove the `NOT NULL` again to try the other route. Add a `CHECK` constraint as `NOT VALID`, which is a quick catalog change, then validate it:

```
ALTER TABLE payments ALTER COLUMN currency DROP NOT NULL;

ALTER TABLE payments
    ADD CONSTRAINT payments_currency_not_null
    CHECK (currency IS NOT NULL) NOT VALID;            -- 0.7 ms

ALTER TABLE payments
    VALIDATE CONSTRAINT payments_currency_not_null;    -- 73.9 ms, scans

ALTER TABLE payments ALTER COLUMN currency SET NOT NULL;
```

```
DEBUG:  existing constraints on column "payments.currency" are sufficient to prove that it does not contain nulls
ALTER TABLE
Time: 0.496 ms
```

Run each statement in its own transaction, as `psql` does in its default autocommit mode. A lock lasts until its transaction ends, so if a migration tool wraps all three in one transaction, the `ACCESS EXCLUSIVE` lock from `ADD CONSTRAINT` covers the whole validation scan. Put the `ADD CONSTRAINT` and the `VALIDATE` in separate migrations.

The validation still scans the table, but it takes a `SHARE UPDATE EXCLUSIVE` lock, which lets reads and writes continue. The [ALTER TABLE documentation](https://www.postgresql.org/docs/16/sql-altertable.html) describes this shortcut for `SET NOT NULL`. Once the column is `NOT NULL`, the check constraint is redundant, and dropping it is another catalog change:

```
ALTER TABLE payments DROP CONSTRAINT payments_currency_not_null;
```

## Order the releases

The `NOT VALID` constraint is enforced on new inserts and updates from the moment it is added. Every writer, including the previous application release and any import job, must already supply a value. Ship that application change first, fill the missing values, then run the three statements. [What NOT VALID means for PostgreSQL CHECK constraints](/learn/postgres-not-valid-constraints/) shows how a `NOT VALID` constraint also rejects updates to other columns of a row that violates it.

PostgreSQL 18 lets `ALTER TABLE` add a `NOT NULL` constraint as `NOT VALID` directly, according to its [release notes](https://www.postgresql.org/docs/release/18.0/). On earlier versions the `CHECK` route above is the way to avoid the long lock.

## What to rehearse

Rehearse on a copy with the production row count, with `client_min_messages = debug1` in the migration session. The rehearsal passes when the `SET NOT NULL` prints the "sufficient to prove" message and not "verifying table". If you see "verifying table", the constraint is missing, not validated, or written in a form that does not exclude nulls. `CHECK (currency IS NOT NULL AND length(currency) = 3)` worked as proof in our run. `CHECK (currency <> '')` did not, because a check passes when its expression is null.

Find the other changes that need a rehearsal in [which PostgreSQL migration needs a rehearsal](/learn/postgres-migration-rehearsal/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [What NOT VALID means for PostgreSQL CHECK constraints](/learn/postgres-not-valid-constraints/)
    
    Breaking migrations The Proofline team November 6, 2026 7 min read
    
-   ### [What a PostgreSQL column default does to existing rows](/learn/postgres-add-column-default/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
