ALTER TABLE changes a table that already exists. The forms you need most are ADD COLUMN, RENAME COLUMN ... TO, DROP COLUMN and ALTER COLUMN ... TYPE. Several actions can go in one statement, separated by commas.
The existing rows get 'free' in plan and NULL in signed_up. Since PostgreSQL 11, adding a column with a constant default does not rewrite the table, so it is fast even on large tables. A volatile default such as clock_timestamp() still rewrites every row.
Rename a column or a table
A rename is instant and keeps the data, indexes and constraints. Views keep working, because they refer to the table's internal id, not its name. Names written as text elsewhere do not follow: PL/pgSQL function bodies and application queries still use the old name, so search for it before renaming.
Drop a column
DROP COLUMN removes the column and its data. It fails if a view or a foreign key depends on the column. CASCADE drops those too, and IF EXISTS skips the error when the column is already gone.
PostgreSQL does not rewrite the table here. It marks the column as dropped, and the space comes back as rows are updated or when the table is rewritten.
Change a column's type
ALTER COLUMN ... TYPE converts every value. When there is no automatic cast from the old type to the new one, add USING with an expression that computes the new value:
Without USING, this statement fails with column "price" cannot be cast automatically to type numeric. If any one value cannot be converted, the whole statement fails and the table is left unchanged.
Most type changes rewrite the table. Some do not: making a varchar(n) longer, or changing varchar to text.
Set or drop a default and NOT NULL
SET NOT NULL scans the table and fails if any row holds a NULL, so fill the gaps first:
DROP DEFAULT and DROP NOT NULL undo them. A new default applies only to future inserts. Existing rows keep their values.
Add a constraint
ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total > 0);
ALTER TABLE orders DROP CONSTRAINT total_positive;
Adding a CHECK or foreign key scans the whole table to validate it. On a large, busy table, add it with NOT VALID first, which checks only new rows. Then run ALTER TABLE orders VALIDATE CONSTRAINT total_positive;. It checks the old rows under a weaker lock that lets reads and writes continue. The foreign keys page uses the same pattern, and the ALTER TABLE reference lists every form and its lock.
Frequently Asked Questions
Can I change the column order in PostgreSQL?
No. ALTER TABLE cannot move a column, and new columns are always added at the end. List the columns in the order you want in your SELECT, or create a new table in that order and copy the data.
Does ALTER TABLE lock the table in PostgreSQL?
Most forms take an ACCESS EXCLUSIVE lock, which blocks reads and writes until the statement finishes. Fast changes (rename, drop column, add a column with a constant default) hold it only briefly. A type change that rewrites the table holds it for the whole rewrite.
How do I add multiple columns at once in PostgreSQL?
Separate the actions with commas in one statement: ALTER TABLE users ADD COLUMN phone text, ADD COLUMN city text;. Every action in one ALTER TABLE runs together, with one lock.
Can ALTER TABLE be rolled back in PostgreSQL?
Yes. DDL in PostgreSQL is transactional, so an ALTER TABLE inside BEGIN ... ROLLBACK is undone completely.