18 ALTER TABLE and DROP

Tables evolve. ALTER TABLE changes an existing table: add or drop a column, rename a column or the table itself, add or drop a constraint, change a column’s type, change its default. DROP TABLE removes the table and every row it contains. Schema changes in PostgreSQL are transactional — a failed ALTER TABLE rolls back cleanly, and you can wrap several ALTERs in a BEGIN ... COMMIT to apply them atomically.

What you’ll learn

• Adding, renaming, and dropping columns
• Adding and dropping constraints
• Changing a column’s type with USING
• Renaming a table
• The locking and rewrite implications of common alterations

Example

-- ADD COLUMN: new columns appear at the end
ALTER TABLE products ADD COLUMN description text;

-- ADD COLUMN with a default (cheap in PG 11+ for constant defaults)
ALTER TABLE products ADD COLUMN sku text NOT NULL DEFAULT '';

-- Rename a column
ALTER TABLE products RENAME COLUMN description TO short_desc;

-- Change a column type (use USING when a cast is needed)
ALTER TABLE products ALTER COLUMN sku TYPE varchar(40);

-- Add a constraint after the fact
ALTER TABLE products ADD CONSTRAINT sku_format CHECK (sku ~ '^[A-Z0-9-]*$');

-- Drop a column
ALTER TABLE products DROP COLUMN short_desc;

-- Rename the table itself
ALTER TABLE products RENAME TO products_old;
ALTER TABLE products_old RENAME TO products;

-- DROP TABLE removes the table and all data
-- (commented out so the rest of the lessons can still use products)
-- DROP TABLE products;

SELECT tablename FROM pg_tables WHERE tablename = 'products';

Each ALTER TABLE is a single transactional statement. You can also batch multiple operations: ALTER TABLE t ADD COLUMN a int, ADD COLUMN b text, DROP COLUMN c; — running them in one statement is faster because the table is touched once.

Two operations are worth understanding in terms of cost:

• Adding a column with no default (or a constant default) is fast — PostgreSQL just records the new column metadata.
• Many column type changes rewrite the table. ALTER COLUMN x TYPE bigint reads and rewrites every row. On a large table this can take hours and holds an exclusive lock the whole time. Some binary-compatible changes avoid a rewrite. For a rewrite-heavy change, plan a migration such as new column → backfill → switch; pg_repack does not perform arbitrary type conversions.

ALTER TABLE ... ALTER COLUMN col TYPE new_type USING expr lets you specify how to convert the existing value: ALTER COLUMN price TYPE numeric(12,2) USING price::numeric(12,2).

Run the example

To run:

$ psql -f source/alter-table-and-drop.sql postgres
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
 tablename
-----------
 products
(1 row)

Common pitfalls

• Adding NOT NULL to an existing column requires that the column already has no NULLs and acquires an ACCESS EXCLUSIVE lock long enough to verify. Backfill first, then SET NOT NULL. From PostgreSQL 12, you can ALTER COLUMN x SET NOT NULL faster if a matching CHECK constraint exists.
• Dropping a column does not reclaim disk space immediately — the column is marked dead. Run VACUUM FULL (heavyweight) or recreate the table to reclaim it.
• Renaming a column updates catalog-tracked dependencies such as views, but can break application SQL and function bodies stored as text. Review those uses before renaming.
• ALTER TYPE on an ENUM cannot remove values; adding values is fine.

Downloads

SQL file: alter-table-and-drop.sql · All lesson files · Setup guide