ALTER TABLE and DROP

Change an existing table's columns, types, defaults and constraints, or remove objects.

Syntax

ALTER TABLE t ADD COLUMN col type [DEFAULT ...] [NOT NULL];
ALTER TABLE t DROP COLUMN col [CASCADE];
ALTER TABLE t RENAME COLUMN old TO new;
ALTER TABLE t RENAME TO new_name;
ALTER TABLE t ALTER COLUMN col TYPE new_type [USING expr];
ALTER TABLE t ALTER COLUMN col SET DEFAULT expr | DROP DEFAULT;
ALTER TABLE t ALTER COLUMN col SET NOT NULL | DROP NOT NULL;
ALTER TABLE t ADD CONSTRAINT name ...;  ALTER TABLE t DROP CONSTRAINT name;
DROP TABLE [IF EXISTS] t [CASCADE];

ADD COLUMN with a constant default is instant in PostgreSQL (no table rewrite).
ADD COLUMN ... NOT NULL needs a DEFAULT if the table already has rows.
ALTER COLUMN ... TYPE converts existing values; when there is no automatic cast use USING expr (e.g. USING amount_text::numeric). It rewrites the table.
SET NOT NULL and ADD CONSTRAINT validate existing rows and fail if any violate them. NOT VALID skips checking old rows (later VALIDATE CONSTRAINT).
DROP ... CASCADE also drops dependent objects (views, foreign keys) — read the notice.
All of this is transactional in PostgreSQL: try it inside BEGIN … ROLLBACK.

Examples

Add, fill and constrain a column (modifies schema) starter

ALTER TABLE customers ADD COLUMN loyalty_tier text DEFAULT 'standard';

UPDATE customers SET loyalty_tier = 'gold'
WHERE customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING count(*) >= 3);

ALTER TABLE customers ALTER COLUMN loyalty_tier SET NOT NULL;

SELECT loyalty_tier, count(*) FROM customers GROUP BY loyalty_tier ORDER BY loyalty_tier;

Existing rows get the default immediately; SET NOT NULL then succeeds.

Change a type with USING (modifies schema) starter

ALTER TABLE products ALTER COLUMN stock TYPE numeric(10,1) USING stock::numeric;
SELECT column_name, data_type, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_name = 'products' AND column_name = 'stock';

USING tells PostgreSQL how to convert each existing value.

Pitfalls

Adding a NOT NULL column without a default to a non-empty table.
Changing a type that some rows cannot be converted to.
DROP ... CASCADE removing views you still need.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: ALTER TABLE t ADD col type (no COLUMN keyword); ALTER COLUMN col newtype; rename with sp_rename.
MySQL: MODIFY COLUMN / CHANGE COLUMN; DDL commits implicitly.
SQLite: Limited ALTER TABLE: RENAME, ADD COLUMN, DROP COLUMN (3.35+); type changes need a table rebuild.