ALTER TABLE

Learning Focus

Use this lesson to apply ALTER TABLE safely: add/rename columns, change types, and introduce constraints with production-friendly steps.

Concept Overview

ALTER TABLE changes an existing table's structure.

Some alterations are fast metadata-only changes; others rewrite the table or require strong locks. Safe migrations minimize lock time and avoid breaking running application code.

Why is it important?

• Schema evolution: apps change, and tables must adapt
• Data integrity: add constraints after data backfills
• Operational safety: avoid long locks and surprise downtime

Where does it fit?

Typical migration patterns:

• expand: add new column/table (nullable)
• backfill: populate data in batches
• enforce: add constraints / set NOT NULL
• contract: remove old columns/tables after cutover

---

Syntax & Rules

Core Syntax

ALTER TABLE table_name <action>;

Common actions:

ALTER TABLE t ADD COLUMN c text;
ALTER TABLE t DROP COLUMN c;
ALTER TABLE t RENAME COLUMN old TO new;
ALTER TABLE t ALTER COLUMN c SET DEFAULT 0;
ALTER TABLE t ALTER COLUMN c DROP DEFAULT;
ALTER TABLE t ALTER COLUMN c SET NOT NULL;
ALTER TABLE t ALTER COLUMN c DROP NOT NULL;
ALTER TABLE t ALTER COLUMN c TYPE bigint USING c::bigint;
ALTER TABLE t ADD CONSTRAINT name CHECK (...) [NOT VALID];
ALTER TABLE t VALIDATE CONSTRAINT name;

Available Options / Parameters

Action	Example	Meaning / Notes
Add column	ADD COLUMN phone text	often safe; adding a constant default is optimized in modern PG
Rename	RENAME COLUMN a TO b	metadata change; update app code accordingly
Change type	ALTER COLUMN x TYPE ... USING ...	may rewrite; USING defines conversion
Add constraint	ADD CONSTRAINT ...	enforce rules; use NOT VALID for large tables
Validate constraint	VALIDATE CONSTRAINT ...	checks existing rows without blocking writes as hard as initial add
Drop column	DROP COLUMN c	destructive; consider phased removal

Key Rules and Considerations

• ALTER TABLE takes locks. The lock level depends on the action.
• Adding NOT NULL on a large table can be disruptive if it must scan/rewrite.
• For large tables, prefer: add nullable column -> backfill -> add constraint/check -> validate -> set not null.
• NOT VALID is available for CHECK and FOREIGN KEY constraints to roll out safely.

---

Step-by-Step Examples

Use this setup for the examples:

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO customers (email) VALUES
  ('a@example.com'),
  ('b@example.com');

Example 1: Add a Nullable Column, Backfill, Then Enforce (Intermediate)

ALTER TABLE customers ADD COLUMN phone text;

UPDATE customers
SET phone = '+1-555-0000'
WHERE email = 'a@example.com';

ALTER TABLE customers
ADD CONSTRAINT customers_phone_nonempty
CHECK (phone IS NULL OR length(phone) >= 7)
NOT VALID;

ALTER TABLE customers
VALIDATE CONSTRAINT customers_phone_nonempty;

Expected output:

ALTER TABLE
UPDATE 1
ALTER TABLE
ALTER TABLE

Explanation:

• Add the column as nullable first (expand).
• Backfill safely.
• Add a constraint as NOT VALID to avoid a full-table validation at the worst time.
• Validate once ready.

---

Example 2: Rename a Column (Beginner)

ALTER TABLE customers
RENAME COLUMN phone TO phone_number;

Expected output:

ALTER TABLE

Explanation:

• Renames are usually fast, but require coordinating application changes.

---

Example 3: Change a Column Type With USING (Advanced)

ALTER TABLE customers ADD COLUMN signup_source text;
UPDATE customers SET signup_source = '1';

ALTER TABLE customers
ALTER COLUMN signup_source TYPE integer
USING signup_source::integer;

Expected output:

ALTER TABLE
UPDATE 2
ALTER TABLE

Explanation:

• USING defines the conversion from old type to new type.
• Type changes can rewrite data; test on realistic sizes.

---

Example 4: Add a Foreign Key Safely (Advanced)

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0)
);

ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

Expected output:

DROP TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE

Explanation:

• NOT VALID allows you to add the FK without scanning the whole table immediately.

---

Practical Use Cases

1) Expand/Contract Deployments

Context: keep old and new app versions working during a rollout.

-- Expand
ALTER TABLE users ADD COLUMN display_name text;

-- Backfill
UPDATE users SET display_name = username WHERE display_name IS NULL;

-- Enforce later
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;

2) Add Constraints Without Breaking Writes Immediately

Context: introduce checks on a large table.

ALTER TABLE payments
ADD CONSTRAINT payments_amount_nonnegative
CHECK (amount >= 0)
NOT VALID;

ALTER TABLE payments VALIDATE CONSTRAINT payments_amount_nonnegative;

3) Rename Columns During a Refactor

Context: move toward consistent naming.

ALTER TABLE customers RENAME COLUMN created TO created_at;

---

Common Mistakes & Troubleshooting

Mistake 1: Adding NOT NULL Immediately on Big Tables

Wrong (often disruptive):

ALTER TABLE big_table ALTER COLUMN c SET NOT NULL;

What happens: long locks or long-running scans.

Fix (phase it):

-- 1) Add column nullable
ALTER TABLE big_table ADD COLUMN c text;

-- 2) Backfill in batches (application/tooling)

-- 3) Add a constraint and validate
ALTER TABLE big_table ADD CONSTRAINT big_table_c_not_null CHECK (c IS NOT NULL) NOT VALID;
ALTER TABLE big_table VALIDATE CONSTRAINT big_table_c_not_null;

-- 4) Optional: set NOT NULL after you know data is clean
ALTER TABLE big_table ALTER COLUMN c SET NOT NULL;

---

Mistake 2: Changing Types Without a Conversion Plan

Wrong:

ALTER TABLE t ALTER COLUMN x TYPE integer;

What happens: errors if values cannot cast.

Fix:

ALTER TABLE t ALTER COLUMN x TYPE integer USING NULLIF(x, '')::integer;

---

Mistake 3: Dropping Columns Too Early

What happens: older app versions break.

Fix: keep old columns during rollout, then remove after all code is updated.

Debugging tips:

1. Use \d+ table_name to confirm the actual table definition.
2. Check locks and long-running operations before running heavy alters.
3. Use NOT VALID + VALIDATE for large-table constraints.
4. Test migrations on a copy with realistic data volume.

---

Best Practices

• ✅ Use expand/contract for production schema changes; ❌ do breaking changes in one deploy.
• ✅ Add nullable columns first and backfill; ❌ add NOT NULL columns without a plan.
• ✅ Use NOT VALID / VALIDATE for large constraints; ❌ validate everything at peak traffic.
• ✅ Always include deterministic USING casts for type changes; ❌ rely on implicit casts.
• ✅ Track all DDL in migrations; ❌ run ad hoc alters in production.

---

Hands-On Practice

Use this setup for the exercises:

DROP TABLE IF EXISTS practice_people;

CREATE TABLE practice_people (
  person_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO practice_people (name) VALUES ('Ada'), ('Linus');

Exercise 1 (Easy): Add a column

Task: Add a nullable nickname column (text).

-- Your SQL here

Solution:

ALTER TABLE practice_people ADD COLUMN nickname text;

---

Exercise 2 (Medium): Rename a column

Task: Rename nickname to display_name.

-- Your SQL here

Solution:

ALTER TABLE practice_people RENAME COLUMN nickname TO display_name;

---

Exercise 3 (Advanced): Add and validate a CHECK constraint

Task: Add a constraint that ensures length(name) >= 2 using NOT VALID, then validate it.

-- Your SQL here

Solution:

ALTER TABLE practice_people
ADD CONSTRAINT practice_people_name_len
CHECK (length(name) >= 2)
NOT VALID;

ALTER TABLE practice_people
VALIDATE CONSTRAINT practice_people_name_len;

---

Connection to Other Concepts

Concept	Why it matters
Transactions	coordinate multi-step schema/data changes
Constraints	added/validated via ALTER TABLE
Indexes	some schema changes require index updates
Locking and concurrency	alters can block readers/writers
Migrations	safe operational process for schema evolution

---

Visual Learning Diagram

flowchart TD
  A[Expand\nADD COLUMN nullable] --> B[Backfill\nUPDATE in batches]
  B --> C[Enforce\nConstraint NOT VALID]
  C --> D[Validate\nVALIDATE CONSTRAINT]
  D --> E[Contract\nDrop old columns]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E allNodes
  class D highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
One-step breaking schema changes	outages	use expand/contract
Heavy alters at peak traffic	long locks	schedule and test
Skipping validation	bad data persists	validate constraints
Unsafe type conversions	data loss/errors	always use USING
Dropping columns early	app failures	keep compatibility during rollout

---

Quick Reference

ALTER TABLE t ADD COLUMN c text;
ALTER TABLE t RENAME COLUMN a TO b;
ALTER TABLE t ALTER COLUMN c TYPE bigint USING c::bigint;
ALTER TABLE t ADD CONSTRAINT name CHECK (...) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT name;

What's Next

• Previous: DROP TABLE - Drop tables safely and handle dependencies.
• Next: Constraints and Indexes - Go deeper on integrity and performance tools.
• Module Overview - Return to the Database Management index.