Postgres by Example: 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

-- 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.
• Changing a column's type rewrites 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. Plan it carefully; tools like pg_repack or doing the change in stages (new column → backfill → swap) are common in production.

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).

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 can break dependent views, functions, and application code. Search before renaming.
• ALTER TYPE on an ENUM cannot remove values; adding values is fine.

Tip: For zero-downtime schema changes, the standard pattern is: add the new column → backfill in batches → make the application write to both → switch reads → drop the old column. Tools like Liquibase, Flyway, or pg_repack help; the database does the locking, you do the choreography.

Try it: Add a column back: ALTER TABLE products ADD COLUMN notes text;. Then rename: ALTER TABLE products RENAME COLUMN notes TO remarks;. Then drop: ALTER TABLE products DROP COLUMN remarks;. Inspect with \d products in psql.

Source: alter-table-and-drop.sql

Next: Primary Keys and Unique

Home: Postgres by Example