Free tools Windows power users keep installed
One-click scans. No signup required.
On PostgreSQL, ALTER TABLE ... ADD COLUMN ... NOT NULL fails on a table with data because existing rows have no value for the new column, and the NOT NULL rule cannot hold for them. The fix depends on one question: should every existing row receive the same value? If yes, a constant default on PostgreSQL 11 or later can be applied quickly. If each row needs its own value, add the column as nullable, backfill it in controlled batches, validate the result, and only then set NOT NULL.
The examples below use PostgreSQL. Syntax and lock behavior differ between engines and versions, so confirm the details for your own engine and release before running anything.
As an Amazon Associate I earn from qualifying purchases.
Why the statement fails
When you add a column, every existing row gets NULL in that column unless a default applies. A NOT NULL constraint says NULL is not allowed, so the two requirements conflict for every row that already exists. PostgreSQL rejects the change rather than invent values. On a populated table you will typically see an error of the form column "fulfillment_state" of relation "orders" contains null values.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteA column default does not solve this by itself. A default tells PostgreSQL what to insert when a new row omits the column. It does not say what value an old row should have. That distinction is why the same DDL behaves very differently depending on whether a default is present and what kind of default it is.
#1 Best Overall
The shortcut: a constant default on PostgreSQL 11 and later
Since PostgreSQL 11, adding a column with a non-volatile constant default does not require every existing row to be rewritten during the ALTER TABLE. PostgreSQL stores the evaluated default in the table metadata and returns it for rows that predate the change. The PostgreSQL 18 documentation puts it this way: “Adding a column with a constant default value does not require each row of the table to be updated when the ALTER TABLE statement is executed.” (PostgreSQL Global Development Group, “Modifying Tables,” PostgreSQL 18 documentation.)
ALTER TABLE orders
ADD COLUMN fulfillment_state text NOT NULL DEFAULT 'pending';This is fast only when the same value is correct for every historical row. If old orders really should read as pending, the statement is appropriate. If the value would be a guess for some rows, the shortcut turns missing domain data into fabricated data that the constraint then protects.
“Non-volatile” is not the same as “literal”
The fast path depends on the default expression being non-volatile, which is a broader category than a literal. Test the exact expression on your version. An expression that is stable but not a plain literal can behave differently from what you assume, so do not infer the behavior from the look of the SQL.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteVolatile defaults force per-row evaluation
A volatile default such as clock_timestamp() must be evaluated separately for each row, so PostgreSQL may need to update or rewrite the table. A statement like the following can be far more expensive than the constant version above, even though it looks almost identical:
ALTER TABLE orders
ADD COLUMN migrated_at timestamptz NOT NULL DEFAULT clock_timestamp();The question to ask is not whether the default is “a function” or “a literal,” but whether PostgreSQL can compute one value and apply it to all old rows.
The migration that works for row-specific values
Use this path when each existing row needs a value computed from its own columns or from a business rule. The steps separate the schema change, the application change, the data work, and the constraint, so that each step can be paused, retried, or verified on its own.
-
Add the column as nullable, with no default. Keep this statement short. ALTER TABLE normally takes an ACCESS EXCLUSIVE lock unless a specific form documents a weaker one, so even a metadata-only change can wait behind active transactions and block the queries queued behind it. Set a lock timeout so the migration fails fast and can be retried instead of queueing indefinitely:
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.SET lock_timeout = '5s'; ALTER TABLE orders ADD COLUMN fulfillment_state text; -
Deploy writers that supply a valid value. Every application version, worker, import job, and administrative script that can insert or update the table must populate the column. During a rolling deployment, older code may still omit it. Either keep a temporary server-side default that is semantically safe for new rows, or wait until every writer is upgraded before enforcing NOT NULL. Do not use a placeholder merely to satisfy the constraint.
-
Backfill old rows in bounded batches. Derive each value from the row’s own contents or from an explicit rule. Walk a stable key range or work queue, commit each batch, and make the job restartable and safe to rerun. Pause or slow the job when latency, WAL generation, replica lag, or lock contention rises. The predicate and value expression below are placeholders that must match your data model:
UPDATE orders SET fulfillment_state = derive_state_from_existing_columns(...) WHERE id > :low_id AND id <= :high_id AND fulfillment_state IS NULL;The batch size and pacing are operational choices that depend on your workload. Measure them in a production-like environment and adjust while watching the job.
-
Verify the data, not just the absence of NULLs. Confirm that no NULLs remain, then check that the derived values are correct against a sample of rows and any business invariants you have. Keep writers protecting new rows during this period so the backlog does not grow again.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Add a NOT VALID check constraint, then validate it. A check added with
NOT VALIDskips the scan of existing rows but is enforced for subsequent inserts and updates. Validation later scans the existing rows under a weaker lock than the immediate scan would require:ALTER TABLE orders ADD CONSTRAINT orders_fulfillment_state_nn CHECK (fulfillment_state IS NOT NULL) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT orders_fulfillment_state_nn;Validation still reads the table and consumes resources, so schedule it with the same care as the backfill.
-
Set the column to NOT NULL. A valid CHECK constraint that proves no NULLs exist lets PostgreSQL skip the full table scan when setting NOT NULL. Confirm in the ALTER TABLE documentation for your version that this optimization applies, and schedule this brief DDL:
ALTER TABLE orders ALTER COLUMN fulfillment_state SET NOT NULL;Keep the check constraint unless you have a reason to drop it. Dropping it is a separate schema change.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Remove temporary defaults and compatibility code after rollout. A default for future inserts and the NOT NULL invariant solve different problems. Keep a default only if it is a true domain default that new rows should receive.
Why the staged path reduces risk, and what it does not
The staged path moves row-by-row work into restartable batches and separates the initial column change from the check on old data. The NOT VALID step is not a waiver of correctness. PostgreSQL enforces the check for new writes from the moment it is added, and VALIDATE CONSTRAINT then checks what already exists.
The approach avoids a single unbounded rewrite or scan during the initial add. It does not promise zero downtime, zero locking, or a fixed duration. The lock behavior of each statement, the I/O from the backfill, the WAL it generates, and the replication lag it can cause all remain. The coordination between application deployments and the migration is still your responsibility.
Choosing between the two paths
| Decision axis | Constant default (PostgreSQL 11+) | Row-specific staged path |
|---|---|---|
| Historical meaning | Every existing row should receive the same correct value | Each row’s value must be derived from its own data or a business rule |
| Work profile | Metadata-only change for non-volatile constant defaults; the DDL lock still applies | Bounded backfill followed by a validation scan, spread over time |
| Main risk | A blanket default that is semantically wrong; version or volatility assumptions that do not hold | Incomplete backfill, writers that have not been upgraded, workload pressure, failed validation |
| Typical use | A genuine domain default that all historical records share, such as a status | Historical values that differ across rows or require computation |
Before PostgreSQL 11, adding a column with a default could require rewriting the table. Review the documentation for your exact version and test on a production-like copy before choosing the shortcut.
Other engines behave differently
Do not carry PostgreSQL’s syntax and lock model to other databases. Microsoft SQL Server documents that a NOT NULL column can be added to a nonempty table only if it has a DEFAULT, and existing rows are populated with that default (Microsoft Learn, “ALTER TABLE (Transact-SQL)”). That is a useful contrast, but it is not a portable recipe. The same constant-versus-row-specific question still applies, while the commands, lock levels, and performance characteristics must be checked against the engine and version you run.
”
The Bottom Line
If every existing row should hold the same value, add the column with a non-volatile constant default on PostgreSQL 11 or later and test the exact expression first. If the value depends on each row, add the column nullable, deploy writers that supply it, backfill in bounded batches, validate with a NOT VALID check, and only then set NOT NULL. Verify the commands and lock behavior against your own PostgreSQL version before running them in production.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
