PostgreSQL ALTER TABLE: add, drop, and modify columns

Quick answer

ALTER TABLE users ADD COLUMN nickname text;
ALTER TABLE users DROP COLUMN nickname;
ALTER TABLE users RENAME COLUMN nickname TO handle;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Unlike MySQL, PostgreSQL supports IF EXISTS and IF NOT EXISTS on column operations, which makes migrations idempotent without catalog lookups.

Adding columns

ALTER TABLE users ADD COLUMN nickname text;
ALTER TABLE users ADD COLUMN IF NOT EXISTS nickname text;
ALTER TABLE users ADD COLUMN created_at timestamptz NOT NULL DEFAULT now();

IF NOT EXISTS makes the statement safe to re-run. That is worth using in migrations by default.

Adding a column with a default is fast

This used to be the classic production footgun and no longer is. Since PostgreSQL 11, adding a column with a non-volatile default does not rewrite the table:

-- 100,000 rows
ALTER TABLE big ADD COLUMN flag boolean NOT NULL DEFAULT true;

ALTER TABLE
Time: 0.669 ms

Sub-millisecond on 100,000 rows. PostgreSQL stores the default in the catalog and materializes it as rows are read, rather than writing every row up front.

Advice from before PostgreSQL 11 told you to add the column nullable, backfill in batches, then set the default. That is now unnecessary work for the common case, and plenty of guides still recommend it.

The exception is a volatile default, which has to be evaluated per row and does force a rewrite. The difference is easy to measure on the same 100,000-row table:

ALTER TABLE big ADD COLUMN ts timestamptz NOT NULL DEFAULT now();

Time: 2.391 ms

ALTER TABLE big ADD COLUMN uid uuid NOT NULL DEFAULT gen_random_uuid();

Time: 161.018 ms

Roughly 67 times slower, and that gap grows with the table. now() is stable within a transaction, so every row gets the same value and PostgreSQL can store it once in the catalog. gen_random_uuid() and random() are volatile: every row needs its own value, so every row must be written.

At 100,000 rows the volatile case is still only 161ms. At 100 million it is an outage, under a lock that blocks reads and writes.

Dropping columns

ALTER TABLE users DROP COLUMN nickname;
ALTER TABLE users DROP COLUMN IF EXISTS nickname;

Dropping a column that is not there is an error, unless you say IF EXISTS:

NOTICE:  column "nope" of relation "t" does not exist, skipping

A notice, not a failure. Migrations can re-run safely.

DROP COLUMN does not reclaim disk space. The column is marked dropped in the catalog and the data stays in each row until those rows are rewritten by VACUUM FULL or a table rewrite. On a big table, dropping a column frees nothing immediately, which surprises people watching disk usage.

Dropping a column also drops anything that depends on it: indexes, constraints, and defaults. If other objects depend on it, such as a view, PostgreSQL refuses:

ERROR:  cannot drop column c of table t because other objects depend on it

CASCADE will drop the dependents too, and will do exactly what you asked, including dropping views you forgot about. Read the error's DETAIL before reaching for it.

Multiple actions in one statement

ALTER TABLE t
  ADD COLUMN d int,
  DROP COLUMN c,
  ALTER COLUMN b SET NOT NULL;

One statement, one lock, one pass. Do this rather than three separate statements: each ALTER TABLE takes its own lock and may make its own pass over the table.

Changing a column type

ALTER TABLE t ALTER COLUMN v TYPE varchar(20);

Widening a varchar is free. Changing to an incompatible type is not:

ALTER TABLE tc ALTER COLUMN v TYPE int;

ERROR:  column "v" cannot be cast automatically to type integer
HINT:  You might need to specify "USING v::integer".

The hint is the answer:

ALTER TABLE tc ALTER COLUMN v TYPE int USING v::integer;

USING takes any expression, so you can transform as you convert:

ALTER TABLE tc ALTER COLUMN v TYPE int USING NULLIF(trim(v), '')::integer;

Two warnings about type changes.

Most rewrite the whole table, taking an ACCESS EXCLUSIVE lock for the duration. On a large table that is an outage. Some are free because the on-disk representation is unchanged: varchar(10) to varchar(20), varchar to text, and numeric to a wider numeric. Narrowing is never free, because every value has to be checked.

A bad USING expression fails partway through, having already rewritten some rows into a new file. The transaction rolls back so your data is safe, but you have burned the time and the lock. Test the expression as a SELECT first:

SELECT v::integer FROM tc;   -- does this succeed for every row?

Constraints

ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT users_age_ck CHECK (age >= 0);
ALTER TABLE users DROP CONSTRAINT users_age_ck;

Always name your constraints. Auto-generated names are predictable but not guaranteed, and a migration that drops users_age_check because that is what it was called in development is a migration that fails somewhere else.

Adding a constraint without a long lock

ADD CONSTRAINT ... CHECK scans the whole table to validate it, holding a lock throughout. NOT VALID skips the scan:

ALTER TABLE users ADD CONSTRAINT users_age_ck CHECK (age >= 0) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_age_ck;

The first statement is near-instant and enforces the constraint on all new and updated rows. The second scans existing rows to prove they comply, taking a much weaker lock that allows concurrent reads and writes.

NOT VALID genuinely does enforce immediately. Given a table that already contains an invalid row:

INSERT INTO ages2 VALUES (-5);                                   -- pre-existing
ALTER TABLE ages2 ADD CONSTRAINT ages2_ck CHECK (age >= 0) NOT VALID;
INSERT INTO ages2 VALUES (-1);                                   -- new row

ERROR:  new row for relation "ages2" violates check constraint "ages2_ck"
DETAIL:  Failing row contains (-1).

The pre-existing -5 is tolerated, and the new -1 is rejected. That is exactly what you want while you clean up historical data: stop the bleeding now, validate later.

This two-step pattern is the standard way to add a constraint to a large live table. It works for foreign keys too.

Adding a unique constraint without a long lock

ADD CONSTRAINT ... UNIQUE builds an index while holding a strong lock. Build the index concurrently first, then adopt it:

CREATE UNIQUE INDEX CONCURRENTLY users_email_idx ON users (email);
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_idx;

CREATE INDEX CONCURRENTLY does not block writes. It cannot run inside a transaction block, which means most migration tools need to be told to run it outside one.

Renaming

ALTER TABLE users RENAME COLUMN nickname TO handle;
ALTER TABLE users RENAME TO app_users;
ALTER TABLE users RENAME CONSTRAINT old_name TO new_name;

Renames are catalog-only and instant, at any table size.

They are also an immediate break for anything still using the old name, which is a feature if you are deprecating something and a problem if you are doing it during a deploy. A rename is not backwards compatible, so the usual safe pattern is: add the new column, write to both, backfill, switch reads, then drop the old one.

Locks, and what actually hurts

Almost every ALTER TABLE takes an ACCESS EXCLUSIVE lock, which blocks reads and writes. What matters is how long it holds it.

The trap is not the duration of the ALTER itself. It is that an ACCESS EXCLUSIVE lock request queues behind existing transactions, and everything else queues behind it. A long-running SELECT can make a millisecond ALTER block your entire application, because the ALTER waits for the SELECT, and every new query waits for the ALTER.

Always set a lock timeout in migrations:

SET lock_timeout = '3s';
ALTER TABLE users ADD COLUMN nickname text;

Failing fast and retrying is much better than an unbounded pile-up. This single line prevents most ALTER TABLE-caused outages.

Common problems

cannot be cast automatically. Add USING expr. Test it as a SELECT first.

cannot drop column ... because other objects depend on it. A view or constraint references it. Read the DETAIL, drop the dependents deliberately, or use CASCADE if you have read what it will take with it.

The ALTER is hanging. It is waiting for a lock, not doing work. Check pg_stat_activity and pg_locks for the blocking transaction. Often it is an idle-in-transaction session.

Disk usage did not drop after DROP COLUMN. Expected. Space is reclaimed when rows are rewritten.

SET NOT NULL is slow. It scans the table. Add a NOT VALID check constraint first, validate it, then SET NOT NULL, which can use the proven constraint to skip the scan on PostgreSQL 12 and later.

Quick reference