Topic 8 of 52
ALTER TABLE
Overview
ALTER TABLE modifies existing table structure — adding/removing columns, changing data types, renaming columns, and managing constraints. It's essential for evolving database schemas without losing data.
Syntax
sql
-- Add column
ALTER TABLE products ADD COLUMN sku VARCHAR(50);
ALTER TABLE products ADD COLUMN tags TEXT[] DEFAULT '{}';
-- Remove column
ALTER TABLE products DROP COLUMN sku;
ALTER TABLE products DROP COLUMN IF EXISTS sku;
-- Rename column
ALTER TABLE products RENAME COLUMN price TO base_price;
-- Change data type (PostgreSQL)
ALTER TABLE products ALTER COLUMN price TYPE DECIMAL(12,2);
-- Set/remove default
ALTER TABLE products ALTER COLUMN in_stock SET DEFAULT TRUE;
ALTER TABLE products ALTER COLUMN discount DROP DEFAULT;
-- Add/drop constraint
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);
ALTER TABLE products DROP CONSTRAINT chk_price;
ALTER TABLE products ADD UNIQUE (sku);
-- Rename table
ALTER TABLE products RENAME TO inventory_items;Common Pitfalls
- Adding a NOT NULL column to a large table can lock the table for minutes — always add as nullable first, backfill, then add the constraint.
- Changing a column's data type may require a full table rewrite — test on a copy first for large tables.
- Interview tip: Never do ALTER TABLE during peak traffic hours on large production tables — it can lock the table and cause outages.
Real-World Example
Adding audit columns to an existing users table as part of a migration:
example
sql
-- Migration: add audit tracking to users table
-- Step 1: Add new columns with safe defaults
ALTER TABLE users
ADD COLUMN updated_at TIMESTAMPTZ DEFAULT NOW(),
ADD COLUMN deleted_at TIMESTAMPTZ, -- NULL = not deleted (soft delete)
ADD COLUMN updated_by INT REFERENCES users(id);
-- Step 2: Backfill existing rows
UPDATE users SET updated_at = created_at WHERE updated_at IS NULL;
-- Step 3: Make column NOT NULL after backfill
ALTER TABLE users ALTER COLUMN updated_at SET NOT NULL;
-- Step 4: Add index for common query pattern
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;
-- Migration: rename column safely
ALTER TABLE orders RENAME COLUMN total TO total_amount;
-- Migration: extend VARCHAR length (usually safe, no rewrite)
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(320);