Module 5 — ALTER TABLE

1. Adding Columns

What it is: ALTER TABLE ... ADD COLUMN adds a new column to an existing table. The new column is appended to the table's structure and can have a data type, default value, and constraints.

Why we use it: Requirements evolve — you might need to store a phone number, add a status field, or track a new metric. Instead of recreating the entire table (which loses data), you can add columns to the existing structure.

When we use it: When new features require new data fields, when migrating data from another system, or when adding audit columns (like updated_at) to existing tables.

-- Add a single column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 
-- Add a column with a default value
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;
 
-- Add a NOT NULL column (requires a default for existing rows)
ALTER TABLE users ADD COLUMN role VARCHAR(20) NOT NULL DEFAULT 'user';

2. Removing Columns

What it is: ALTER TABLE ... DROP COLUMN permanently removes a column and all of its data from a table. The column is completely deleted — there's no way to recover the data without a backup.

Why we use it: Over time, tables accumulate unused columns from deprecated features. Removing them simplifies the schema, reduces storage, and makes queries cleaner.

When we use it: When a feature is removed and the column is no longer needed, during schema cleanup, or when preparing for production deployments.

-- Remove a column
ALTER TABLE users DROP COLUMN phone;
 
-- Safe removal (prevents error if column doesn't exist)
ALTER TABLE users DROP COLUMN IF EXISTS phone;

3. Renaming Columns and Tables

What it is: ALTER TABLE ... RENAME changes the name of a column or table without affecting the data. The rename is instant and doesn't require rewriting the table.

Why we use it: Naming conventions evolve, or you might realize a column name is confusing. Renaming improves readability and consistency without losing data.

When we use it: During refactoring, when adopting naming conventions, or when a column name is misleading (e.g., name → full_name).

-- Rename a column
ALTER TABLE users RENAME COLUMN username TO user_name;
 
-- Rename a table
ALTER TABLE users RENAME TO accounts;

4. Changing Column Types

What it is: ALTER TABLE ... ALTER COLUMN ... TYPE changes the data type of an existing column. PostgreSQL will attempt to convert existing data to the new type, and fail if conversion isn't possible.

Why we use it: Requirements change — you might need to store longer text, use a more precise numeric type, or switch from INTEGER to BIGINT as data grows.

When we use it: When the current data type is too restrictive (e.g., VARCHAR(50) is too short), when optimizing storage, or when migrating data from a system with different types.

-- Change VARCHAR length
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);
 
-- Change from INTEGER to BIGINT (for growing tables)
ALTER TABLE products ALTER COLUMN id TYPE BIGINT;
 
-- Change from TEXT to VARCHAR (adds length constraint)
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

5. Adding and Dropping Constraints

What it is: You can add or remove constraints (UNIQUE, CHECK, FOREIGN KEY, etc.) after a table is created, without recreating the table. This is essential for evolving your schema as business rules change.

Why we use it: Constraints enforce data integrity. You might realize a column should be unique, or that a foreign key is needed to link tables. Adding constraints after creation lets you tighten rules without losing data.

When we use it: When adding new validation rules, when linking tables that weren't previously related, or when tightening security requirements.

Adding constraints

-- Add a UNIQUE constraint
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);
 
-- Add a CHECK constraint
ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0);
 
-- Add a FOREIGN KEY
ALTER TABLE posts ADD CONSTRAINT fk_posts_users
    FOREIGN KEY (user_id) REFERENCES users(id);

Dropping constraints

What it is: ALTER TABLE ... DROP CONSTRAINT removes a named constraint from a table. The constraint name is required — you can find it with \d table_name.

Why we use it: Constraints can become outdated or too restrictive. You might need to temporarily disable a constraint for data migration, or permanently remove a business rule that no longer applies.

When we use it: When a business rule changes, during data migrations, or when a constraint is causing performance issues.

-- Drop a constraint by name
ALTER TABLE users DROP CONSTRAINT uq_users_email;
 
-- Safe removal
ALTER TABLE users DROP CONSTRAINT IF EXISTS uq_users_email;

Finding constraint names

What it is: PostgreSQL auto-generates constraint names if you don't specify one. To drop or modify a constraint, you need to know its name. You can find it using the \d meta-command or by querying the information schema.

Why we use it: You can't drop a constraint without knowing its name. Finding constraint names is essential for debugging and schema management.

When we use it: Whenever you need to drop, modify, or reference a constraint.

-- View all constraints on a table
\d users
 
-- Or query the information schema
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'users';

6. Setting and Dropping Defaults

What it is: You can change or remove the default value of a column after creation. This affects future inserts — existing rows are not modified.

Why we use it: Business rules change — you might want new users to have a different default role, or new orders to start with a different status.

When we use it: When changing the default behavior for new records, during feature launches, or when adjusting business logic.

-- Set a default value
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'user';
 
-- Remove a default value
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;

Common Mistakes to Avoid

1. Dropping columns with dependent objects — Views, indexes, or constraints may reference the column. Use CASCADE to drop them too.
2. Renaming columns used in code — Your application code still references the old name. Coordinate with your team.
3. Changing types on large tables — Type changes on tables with millions of rows can lock the table for a long time. Use ALTER TABLE ... ALTER COLUMN ... TYPE ... USING ... for conversions.

Practice Exercise

Using your courses table:

1. Add a column difficulty with type VARCHAR(20) and default 'beginner'
2. Add a column enrollment_count as an integer, defaulting to 0
3. Rename instructor to instructor_name
4. Add a CHECK constraint that enrollment_count >= 0
5. Verify all changes with \d courses
6. Drop the enrollment_count column
7. Verify it's gone with \d courses