Adding a NOT NULL Column to a Populated Table — the Expand/Contract Pattern

Published Jul 7, 2026
by

The ALTER TABLE ran through cleanly on your machine — on staging, the same deploy fails with column "country_code" of relation "customer" contains null values. The difference is not the SQL, it is the data: the local table was empty, the one on staging was not. If you want to add a NOT NULL column to an existing table that already holds rows, you need more than one statement — you need the right order, and you need it across the whole deploy.

The essentials up front:

  • Why the direct approach breaks: ADD COLUMN … NOT NULL fails on every populated environment — and the DEFAULT shortcut does run through, but silently writes a placeholder into every existing row.
  • The three-step sequence: add the column nullable → backfill → SET NOT NULL — the expand/contract pattern in its most common form.
  • How the sequence maps onto a deployment model: object file plus run-once transition — and how much of that carries over to Flyway or Liquibase.
  • Greenfield safety: the same scripts must also run through on an empty, freshly created database.
  • What SET NOT NULL costs in locks on large tables — and the escape route via CHECK … NOT VALID.

Prerequisites: Postgres and a SQL client. All examples are verified against Postgres 16 in a throwaway Docker container; the pattern applies from Postgres 12 onward, most of the statements from Postgres 11. The deployment building blocks come from the open DI² starter kit on GitHub.

Contents

Why the Direct Approach Breaks

The starting point for all examples: a customer table with three existing rows, the way it sits on every environment after go-live. The new column country_code is supposed to join it — and it is supposed to be NOT NULL.

  1: CREATE TABLE IF NOT EXISTS app.customer
  2: (
  3:     id     bigint         NOT NULL GENERATED ALWAYS AS IDENTITY
  4:    ,name   varchar(200)   NOT NULL
  5:
  6:    ,CONSTRAINT pk_customer PRIMARY KEY (id)
  7: );
  8:
  9: INSERT INTO app.customer
 10:     (
 11:         name
 12:     )
 13: VALUES
 14:     ('Alpha GmbH')
 15:    ,('Beta AG')
 16:    ,('Gamma KG');

Trap 1: NOT NULL without a default. The most obvious route breaks immediately — Postgres would have to fill the new column with NULL in the three existing rows, and that is exactly what the constraint forbids:

  1: ALTER TABLE app.customer ADD COLUMN country_code varchar(2) NOT NULL;
  2: -- ERROR:  column "country_code" of relation "customer" contains null values

On the empty development database this error never shows up. It waits for the first environment that has data — staging if you are lucky, production if you are not.

Trap 2: NOT NULL with DEFAULT. The shortcut that almost every search result recommends does run through — and that is precisely its problem:

  1: ALTER TABLE app.customer ADD COLUMN country_code varchar(2) NOT NULL DEFAULT 'XX';
  2:
  3: SELECT
  4:     name
  5:    ,country_code
  6: FROM
  7:    app.customer;
  8: -- Alpha GmbH | XX
  9: -- Beta AG    | XX
 10: -- Gamma KG   | XX

Every existing row now carries the placeholder XX — no error message, no log entry, and nobody ever had to decide it. That is fine when the default is the factually correct value for every old row (an is_verified boolean NOT NULL DEFAULT false, say). For a column like country_code, whose value really should come from the data row by row, it is a silent data lie: the constraint is satisfied, the information is still missing.

One popular counter-argument no longer applies, by the way: since Postgres 11, ADD COLUMN with a constant default is a pure catalog entry — the table is not rewritten, no matter how big it is. So the trap is not a performance trap but a semantic one: the problem is not the ALTER TABLE, it is the placeholder values it leaves behind.

Trap 3: SET NOT NULL before the backfill. Whoever knows the first two traps splits the steps — and walks into the third the moment the order is wrong:

  1: ALTER TABLE app.customer ADD COLUMN IF NOT EXISTS country_code varchar(2) NULL;
  2:
  3: ALTER TABLE app.customer ALTER COLUMN country_code SET NOT NULL;
  4: -- ERROR:  column "country_code" of relation "customer" contains null values

This happens faster than it looks: the column sits in the table definition, the backfill in a separate script — and the deployment tooling executes the table definition first. The constraint takes effect before the data is there. This ordering mistake is exactly why, on a populated table, the solution is not a single statement but a sequence.

The Three-Step Sequence

This is how the NOT NULL column lands on every environment — populated or empty:

  1. Expand: Add the column nullable — ALTER TABLE … ADD COLUMN country_code varchar(2) NULL.
  2. Backfill + constraint: Fill the existing rows with an UPDATE, then — in the same script, right after — ALTER COLUMN country_code SET NOT NULL.
  3. Contract: After the rollout, fold the final state back into the schema definition so it fully describes the table again.

Steps 1 and 2 are the actual expand/contract pattern: first extend without breaking anything that exists — then bring the data along — only tighten at the very end. The constraint comes last, once every row can satisfy it. Step 3 is the bookkeeping afterwards: it changes nothing in the database anymore, but it keeps the versioned schema definition from permanently trailing reality.

How these three steps are distributed across concrete files depends on the deployment model — that is the next section.

Step by Step in the Deployment Model

The example uses the convention-based deployment model from Deploying a SQL Schema Without a Migration Tool: object files describe the desired state of each table and are idempotent — they run on every deploy. Run-once transitions handle data-dependent work — they run exactly once per database. The three-step sequence splits precisely along that line.

Step 1 — the column goes into the object file, nullable:

  1: -- Convergent evolution: columns added after the initial CREATE
  2: ALTER TABLE app.customer ADD COLUMN IF NOT EXISTS country_code varchar(2) NULL;

The IF NOT EXISTS makes the line repeatable at will: an existing environment gets the column on the next deploy, an environment that already has it skips the line. What matters is the NULL at the end — the object file must not set the NOT NULL here, or trap 3 is right back on the table: the object file runs before the backfill.

Step 2 — backfill and SET NOT NULL go into a run-once transition:

  1: -- postdeploy/202607061000.backfill-customer-country-code.sql
  2: UPDATE app.customer
  3: SET
  4:    country_code = 'DE'
  5: WHERE
  6:    country_code IS NULL;
  7:
  8: -- Only after the backfill — at the end of the same script
  9: ALTER TABLE app.customer ALTER COLUMN country_code SET NOT NULL;

The backfill is deliberately kept simple here — a business decision (“existing customers are domestic”) as a fixed value. In practice the UPDATE often derives the value from existing columns or a reference table; that changes nothing about the pattern.

Two things are decisive: The SET NOT NULL sits at the end of the same script — so it can never run before its backfill. And the script runs exactly once per database; after that, the constraint itself makes sure no new row without a country_code can appear. How the deploy remembers which transition has already run — checksums, immutability, a tracking table — is a topic of its own: Tracking Schema Changes Without a Framework.

Step 3 — after the rollout, fold the desired state back in:

  1: -- Convergent evolution: columns added after the initial CREATE
  2: ALTER TABLE app.customer ADD COLUMN IF NOT EXISTS country_code varchar(2) NULL;
  3: ALTER TABLE app.customer ALTER COLUMN country_code SET NOT NULL;

Once the transition has run on all environments, the SET NOT NULL moves into the object file as an idempotent line. On existing environments, line 3 is a no-op — the column has long been NOT NULL there. Its value shows elsewhere: the object file describes the table completely again, and a freshly created environment does not depend on the effect of an old transition script. Both paths were verified against a Docker Postgres — the object file twice in a row (idempotency), the transition on a populated and on an empty database.

And with Flyway or Liquibase? The pattern stays, the file layout changes. Because a migration tool executes every migration exactly once anyway, all three steps technically fit into one migration: add the column nullable, backfill, SET NOT NULL. That holds as long as the application that populates the new column ships in the same release. You have to split it into several migrations once older application versions keep running during the rollout: then the old code still writes rows without a country_code, and the SET NOT NULL has to wait until every writer knows the column — expand in release N, contract in release N+1. The ordering rule is the same in both worlds; only the boundary that enforces it shifts from the file type to the release planning.

Greenfield Safety

One detail decides whether the transition is a one-way script or a permanently valid building block: it must also run through without errors on an empty, up-to-date database. Because a new environment — the next developer’s laptop, a fresh CI run, a new staging — walks through the same transitions on its first deploy, once, in chronological order.

The WHERE guard in the backfill takes care of that:

  1: UPDATE app.customer
  2: SET
  3:    country_code = 'DE'
  4: WHERE
  5:    country_code IS NULL;

On the empty table the UPDATE hits zero rows and passes as a no-op; the subsequent SET NOT NULL holds trivially — there is no row that could violate it. The guard serves a second purpose on top: it never overwrites a value that a user has already set between adding the column and running the backfill.

The counter-check is worth making a fixed part of the workflow: deploy the complete schema including transitions against a throwaway container — once on an empty database, once twice in a row. The double deploy proves the idempotency of the object files, the empty-database deploy proves the greenfield safety of the transitions. In CI, exactly that can be automated as a pull-request gate: GitHub Actions for Postgres Deploys.

What SET NOT NULL Costs on Large Tables

Honesty is part of the deal: the three-step sequence solves the ordering problem, not the locking problem. ALTER COLUMN … SET NOT NULL takes an ACCESS EXCLUSIVE lock on the table and holds it while Postgres reads every row to validate the constraint. For the duration of that scan the table is fully blocked — including reads. How long that takes depends on table size, storage and cache — on small and medium tables typically seconds and a non-issue inside a normal deploy window; on very large tables, or under strict availability requirements, the scan under an exclusive lock becomes a risk.

On top of that comes an effect that in practice often weighs more than the scan itself: the ACCESS EXCLUSIVE lock first has to become available. If a long transaction is still running on the table, the ALTER TABLE waits for it — and behind the waiting ALTER, every further query queues up, including every SELECT. Setting a lock_timeout before the ALTER caps that risk: the deploy then aborts in a controlled way instead of stalling the application.

For that case, Postgres has offered an escape route since version 12: an already validated CHECK constraint that proves IS NOT NULL spares the SET NOT NULL its scan. The locking load shifts into operations that are far more tolerable:

  1: ALTER TABLE app.customer ADD CONSTRAINT chk_customer_country_code_nn
  2:    CHECK (country_code IS NOT NULL) NOT VALID;
  3:
  4: UPDATE app.customer
  5: SET
  6:    country_code = 'DE'
  7: WHERE
  8:    country_code IS NULL;
  9:
 10: ALTER TABLE app.customer VALIDATE CONSTRAINT chk_customer_country_code_nn;
 11:
 12: ALTER TABLE app.customer ALTER COLUMN country_code SET NOT NULL;
 13:
 14: ALTER TABLE app.customer DROP CONSTRAINT chk_customer_country_code_nn;

Lines 1 and 2 create the constraint as NOT VALID — a brief catalog entry without any data check; it applies to new rows only from that point on. The VALIDATE CONSTRAINT in line 10 does the expensive scan, but holds a lock that lets concurrent reads and writes carry on. The SET NOT NULL in line 12 then finds a validated constraint in place and adopts it without a scan of its own — the exclusive lock lasts only an instant. Line 14 clears the helper constraint away again. Details in the Postgres documentation on ALTER TABLE.

For most tables this five-step dance is overkill — the plain transition from the previous section is enough. But it is good to know the pattern scales instead of collapsing at the first big table.

Related Cases — the Same Pattern

The three-step sequence is the most common member of a whole family: whenever a schema change depends on the existing data, it decomposes into expand → bring the data along → contract.

  • Adding a UNIQUE constraint later: First clean up the duplicates (the backfill counterpart, as a run-once transition), then create the constraint. Setting it directly replays trap 3 in a new disguise: could not create unique index on every environment with duplicates.
  • Adding a foreign key later: First resolve orphaned references, then create the FOREIGN KEY — on large tables again in two stages, as NOT VALID + VALIDATE CONSTRAINT.
  • Narrowing a column type (say varchar(100) to varchar(2)): First bring the values into the new format, then change the type. If the rework is bigger: add a new column, fill it, switch over, drop the old column later.
  • Renaming a column with the application running: RENAME COLUMN itself is a pure catalog entry — but old application code only knows the old name. Zero downtime here means: new column, synchronization, switch the code, drop the old column — expand/contract across two releases.

The common denominator: the constraint, or the target state, always comes last, after the data. Internalize that one rule and you can derive the remaining cases yourself.

FAQ

How do I add a NOT NULL column to an existing table in Postgres?

In three steps: add the column nullable first (ALTER TABLE … ADD COLUMN … NULL), fill the existing rows with an UPDATE, then set the constraint with ALTER TABLE … ALTER COLUMN … SET NOT NULL. The direct route — ADD COLUMN … NOT NULL in one statement — fails as soon as the table contains rows, because Postgres must not fill the new column with NULL. On small tables all three steps may run in a single transaction — DDL is transactional in Postgres; the table then stays exclusively locked for the duration of the transaction, though.

Why not simply ADD COLUMN … NOT NULL DEFAULT …?

Because the default then silently ends up in every existing row. That is correct when the value factually applies to all old rows — false for a new flag, say. It is wrong when the value really should come from the data row by row: a placeholder then satisfies the constraint, but the information is still missing — just harder to spot than before. Since Postgres 11 the operation is fast even on large tables, by the way; the argument against it is semantic, not technical.

What is the expand/contract pattern?

A strategy for decomposing schema changes into backwards-compatible phases: first expand (the new column arrives nullable, nothing existing breaks), then bring data and code along (the backfill), only then contract (the NOT NULL, dropping the old column). The term comes from the world of zero-downtime deployments and is also known as parallel change.

How long does ALTER TABLE lock the table?

That depends on the sub-step. ADD COLUMN — nullable or with a constant default — has been a pure catalog entry since Postgres 11: an exclusive lock, but only for milliseconds. SET NOT NULL, on the other hand, holds the exclusive lock for a complete table scan. On large tables the scan can be moved out of the exclusive lock: create a CHECK … NOT VALID, validate it concurrently with VALIDATE CONSTRAINT, and SET NOT NULL then applies without a scan of its own — see the section on locks.

Does the pattern work in SQL Server too?

Yes. There as well, ALTER TABLE … ADD … NOT NULL without a default breaks on a populated table; the default variant writes the placeholder into the existing rows via WITH VALUES — the same semantic trap. The clean sequence is identical: add the column nullable, fill it with an UPDATE, then ALTER TABLE … ALTER COLUMN … NOT NULL (in SQL Server with the full data type spelled out). One detail to check up front: whether adding a NOT NULL DEFAULT column runs as a pure metadata operation or rewrites the table depends on version, edition and the default expression there.