iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
In PostgreSQL, ALTER TABLE ... ADD COLUMN ... NOT NULL fails on a table with existing rows because a new column without a default holds NULL for every old row, and those rows would violate the NOT NULL rule the moment it is added. The fix depends on what the old rows should contain. If every historical row should share one constant value, PostgreSQL 11 and later can add that column without rewriting each row. If each row needs its own value, add the column as nullable, backfill it in controlled batches, and tighten the rule in stages. This article uses PostgreSQL as its engine. Confirm the syntax and lock behavior against your own version, and see the SQL Server note near the end if you run a different engine.
Why the statement fails on a table with data
Suppose you run this on an orders table that already has rows:
ALTER TABLE orders ADD COLUMN fulfillment_state text NOT NULL;PostgreSQL has to make the new column valid for every existing row at the moment it is added. Without a default, each old row receives NULL, which breaks NOT NULL, so the statement is rejected with an error of the form column "fulfillment_state" of relation "orders" contains null values. The table being empty would make the statement succeed, which is why it often works in development and fails in production.
A DEFAULT changes the outcome, but it changes it for a different reason than many people expect. A default tells PostgreSQL which value to supply when an insert omits the column. Adding a default in the same statement also gives existing rows a value, so the NOT NULL check passes. Whether that value is correct for those historical rows is a separate question, and that question decides which migration you should use.
#1 Best Overall
The shortcut: a constant default on PostgreSQL 11 and later
The PostgreSQL 18 documentation states: “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, PostgreSQL 18 documentation, “Modifying Tables,” https://www.postgresql.org/docs/current/ddl-alter.html.)
In practice, PostgreSQL 11 and later evaluate a non-volatile default once, store the result in table metadata, and return that value for rows that existed before the column was added. This makes the following statement fast even on a large table:
ALTER TABLE orders
ADD COLUMN fulfillment_state text NOT NULL DEFAULT 'pending';Fast does not mean lock-free. The statement still takes the ACCESS EXCLUSIVE lock that ALTER TABLE uses by default, so it can wait behind long-running transactions and hold up other queries while it waits.
When the shortcut is correct
- Every existing row should genuinely carry the same value, for example a status that all historical records truly share.
- The default expression is non-volatile. Test the exact expression rather than assuming that a literal-looking expression qualifies, because the rule depends on volatility, not on appearance.
- Your PostgreSQL version is 11 or later. Check the version with
SHOW server_version;. - Your deployment can tolerate the brief ACCESS EXCLUSIVE lock.
When it is not
- Volatile defaults. A default such as
clock_timestamp()is evaluated for each row. That requires per-row work and can force an update or rewrite of the table. - Blanket placeholders. A value such as
'unknown'or'pending'may satisfy the constraint while misstating what happened to the old rows. Reports and downstream logic will then treat invented values as real history. - PostgreSQL versions before 11. Adding a default can require rewriting the table. Review the documentation for your exact version and test on a production-like copy first.
Choosing between the two paths
| Decision point | Constant default (PostgreSQL 11+) | Row-specific staged migration |
|---|---|---|
| Historical meaning | Every old row should receive the same correct value | Each old row needs a value derived from its own data or a business rule |
| Work at DDL time | Metadata change for eligible non-volatile defaults; no per-row update during ALTER TABLE | Short nullable column add, then separate row updates, then a validation scan |
| Lock profile | ACCESS EXCLUSIVE for the ALTER TABLE statement | ACCESS EXCLUSIVE for each short DDL step; VALIDATE CONSTRAINT uses SHARE UPDATE EXCLUSIVE, which permits ordinary reads and writes to continue |
| Main risk | A wrong blanket default, or a volatility or version assumption that does not hold | Incomplete backfill, writers that do not yet set the value, batch pressure on the workload, validation failures |
| Typical fit | A genuine domain default that all historical rows share | Historical values that differ, or that must be computed |
Lock descriptions reflect the PostgreSQL 17 and 19 ALTER TABLE references (https://www.postgresql.org/docs/17/sql-altertable.html and https://www.postgresql.org/docs/19/sql-altertable.html). Check the reference for your version before relying on them.
The staged migration for row-specific values
Use this path when the old rows need values that differ from one another. Before you start, set a lock timeout for the session running each DDL statement, so that a migration fails fast instead of queuing behind a long transaction and blocking every query that arrives after it:
SET lock_timeout = '3s';If the statement times out, retry it later. Do not raise the timeout to make the migration wait.
Step 1: Add the column as nullable, without a default
ALTER TABLE orders ADD COLUMN fulfillment_state text;Because no rows need values yet, this statement does not have to satisfy NOT NULL. It still takes a brief ACCESS EXCLUSIVE lock, so keep it short and run it inside your lock-timeout setting.
Step 2: Make every writer supply a valid value
Update every application version, background worker, import job, and administrative script that can insert or update the table so that it sets fulfillment_state to a valid value. During a rolling deployment, old code may still omit the column. In that window, you can add a temporary default that is a real domain default for new rows, but do not use a fake placeholder just to satisfy the constraint. Remove any temporary default in Step 7.
Step 3: Backfill old rows in bounded batches
Derive each value from the row’s existing columns or from an explicit business rule. The following example is illustrative only. It assumes that a shipped order has a shipped_at timestamp, and your own mapping will differ:
UPDATE orders
SET fulfillment_state = CASE
WHEN shipped_at IS NOT NULL THEN 'shipped'
ELSE 'awaiting_shipment'
END
WHERE id > 1000000 AND id <= 1010000
AND fulfillment_state IS NULL;Run the job over stable key ranges, committing each batch separately. Because the predicate filters on fulfillment_state IS NULL, a batch can be rerun safely after an interruption, and rows that already received a value from the upgraded writers are skipped. Pause or slow the job when query latency, WAL generation, or replica lag rises. The batch size and pacing are operational choices for your workload, so measure them on a representative environment before you run the job in production.
Step 4: Prove there are no NULLs and that the values are correct
SELECT count(*) FROM orders WHERE fulfillment_state IS NULL;
SELECT count(*) FROM orders
WHERE shipped_at IS NOT NULL AND fulfillment_state <> 'shipped';The first query should return 0. The second checks the derived values against the rule. Adapt it to your own mapping. A column that is populated but wrong passes the NOT NULL check and still causes damage, so verify meaning as well as presence.
Free tools Windows power users keep installed
One-click scans. No signup required.
Step 5: Add a NOT VALID check, then validate it
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;NOT VALID skips the initial scan of existing rows, but PostgreSQL enforces the check for every later insert and update. VALIDATE CONSTRAINT then scans the existing rows. It takes SHARE UPDATE EXCLUSIVE, which lets ordinary reads and writes continue, so this is the heavier-looking step that is safer to run under production load.
The check must be written so that it can actually prove the absence of NULLs. A CHECK constraint passes when its expression evaluates to TRUE or NULL. The expression fulfillment_state IS NOT NULL returns false for a NULL value and is therefore a valid proof. A weaker check such as fulfillment_state <> '' returns NULL for a NULL value, which passes, so it proves nothing about NULLs.
Step 6: Set the column to NOT NULL
ALTER TABLE orders
ALTER COLUMN fulfillment_state SET NOT NULL;PostgreSQL documents an optimization in which a valid CHECK constraint that proves there are no NULLs allows SET NOT NULL to skip the full table scan. Confirm that your version’s ALTER TABLE reference describes this behavior before you schedule the change, since the lock on this statement is still taken and the window still matters. Keep the CHECK constraint in place unless you have a separate reason to drop it. Dropping it is its own schema change.
Step 7: Remove temporary defaults after the rollout completes
Once every writer sets the value, drop any default that existed only for old code:
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →ALTER TABLE orders
ALTER COLUMN fulfillment_state DROP DEFAULT;Keep a default only if it is a true domain default for new rows. The default for future inserts and the NOT NULL rule solve different problems, and one does not replace the other.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What staging reduces, and what it does not change
The staged path avoids a single unbounded rewrite or scan during the initial column addition. It moves the row-by-row work into restartable batches, and it separates the schema change from the check of old data. That separation is the real benefit.
- It does not give you zero locking. Each DDL step takes its own lock, and a waiting DDL statement can block the queries behind it.
- It does not give you zero downtime or a predictable duration. The backfill competes for I/O, generates WAL, and can increase replication lag and application latency.
- It does not remove the need for deployment coordination. Writers must be upgraded before the NOT NULL rule is enforced, or old code will start failing.
- It does not make validation free. VALIDATE CONSTRAINT still reads the table.
Troubleshooting the common failures
- SET NOT NULL reports that the column contains null values. The backfill is incomplete, or a writer that does not set the value is still active. Rerun the NULL count from Step 4 and find the writer that inserted the row.
- The DDL statement waits, and queries pile up behind it. The lock timeout was reached, or a long transaction is holding a conflicting lock. Identify the oldest open transactions with
SELECT pid, state, now() - xact_start AS age, left(query, 80) FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY age DESC;, then retry the DDL once the blocker finishes. - VALIDATE CONSTRAINT fails. Some existing rows still contain NULL. Find them with the query from Step 4, correct the data, and validate again. Do not drop the check to get past the failure.
- Old application code fails after the NOT NULL rule is enforced. An insert path does not yet supply the value. Upgrade or fix that writer. The temporary default from Step 2 can cover it only while it remains in place.
The SQL Server contrast
Microsoft Learn states that a NOT NULL column can be added to a nonempty table if it has a DEFAULT, and that existing rows are populated with that default (https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver17). The question of whether one value truly fits every historical row applies there as well. The PostgreSQL steps above do not translate directly. Their syntax, the NOT VALID and VALIDATE behavior, and the lock behavior are specific to PostgreSQL. Confirm the target engine, version, storage engine, and DDL algorithm before you adapt any of these commands.
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.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
