Chapter 11: PostgreSQL ALTER COLUMN
PostgreSQL ALTER COLUMN — one of the most frequently used (and sometimes most nerve-wracking) commands when your database schema needs to evolve after data is already inside.
You’ve already learned:
- CREATE TABLE
- INSERT
- SELECT
- ADD COLUMN
- UPDATE
Now comes ALTER COLUMN — the tool to modify properties of an existing column (not add or drop the column itself — that’s separate).
1. What is ALTER COLUMN? (Honest teacher explanation)
ALTER COLUMN is a clause inside ALTER TABLE that lets you change attributes of one specific column that already exists.
Common things you change with it:
- Data type (TYPE)
- Default value (SET DEFAULT / DROP DEFAULT)
- NOT NULL constraint (SET NOT NULL / DROP NOT NULL)
- (In newer versions) generated / identity properties
- Collation (rare)
- Storage parameters (advanced)
Important reality in PostgreSQL 18 (2026):
- Changing data type is the most common use — but it can be dangerous if the conversion fails or loses data.
- PostgreSQL is very strict: if the change would make existing data invalid → it errors and nothing happens.
- For type changes, you almost always need a USING expression when the old and new types are not implicitly castable.
- On large tables → type changes can take time and lock the table (rewrite in older versions; more optimized now but still careful).
Official syntax (from PostgreSQL 18 docs — postgresql.org/docs/current/sql-altertable.html):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
ALTER TABLE table_name ALTER [ COLUMN ] column_name action [, ...]; Where action is one of: [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ] SET DEFAULT expression DROP DEFAULT SET NOT NULL DROP NOT NULL ... (more advanced like SET GENERATED, SET STATISTICS, etc.) |
You can chain multiple actions in one command (comma-separated).
2. Real example — evolving our students table again
Assume our current students table looks like this (after previous lessons):
|
0 1 2 3 4 5 6 |
\d students |
Columns include:
- id → BIGSERIAL PRIMARY KEY
- first_name → VARCHAR(50) NOT NULL
- last_name → VARCHAR(50) NOT NULL
- email → VARCHAR(100) UNIQUE NOT NULL
- gpa → NUMERIC(3,2)
- enrollment_year → SMALLINT NOT NULL DEFAULT EXTRACT(YEAR FROM CURRENT_DATE)
- fees_paid → BOOLEAN DEFAULT FALSE
- phone_number → VARCHAR(15)
- … etc.
Now the college requirements change — let’s fix some issues.
Example 1: Change data type (most common & most careful operation)
Problem: enrollment_year was SMALLINT but now we want to store future years beyond 32767 → change to INTEGER.
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students ALTER COLUMN enrollment_year TYPE INTEGER; |
→ This works automatically because SMALLINT → INTEGER is safe & implicit cast exists.
Harder example: Change GPA from NUMERIC(3,2) to NUMERIC(4,2) to allow 10.00 exactly
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students ALTER COLUMN gpa TYPE NUMERIC(4,2); |
→ Also safe — precision increases.
Tricky type change: Change phone_number from VARCHAR(15) to TEXT (unlimited length)
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students ALTER COLUMN phone_number TYPE TEXT; |
→ Safe.
Dangerous example: Change string to number (needs USING)
Suppose we have a bad old column old_gpa_text VARCHAR(10) with values like ‘8.75’, ‘9.2’
|
0 1 2 3 4 5 6 7 8 |
ALTER TABLE students ALTER COLUMN old_gpa_text TYPE NUMERIC(4,2) USING (old_gpa_text::NUMERIC); |
→ USING tells Postgres how to convert old values.
If some values are invalid (e.g. ‘abc’) → error. Fix first:
|
0 1 2 3 4 5 6 |
UPDATE students SET old_gpa_text = NULL WHERE old_gpa_text !~ '^[0-9.]+$'; |
Then run the ALTER.
Example 2: Add / remove NOT NULL constraint
|
0 1 2 3 4 5 6 7 8 |
-- Make phone_number required now ALTER TABLE students ALTER COLUMN phone_number SET NOT NULL; |
→ Fails if any row has NULL phone_number.
Safe way:
|
0 1 2 3 4 5 6 7 8 9 10 |
-- First fill NULLs UPDATE students SET phone_number = 'N/A' WHERE phone_number IS NULL; -- Then ALTER TABLE students ALTER COLUMN phone_number SET NOT NULL; |
Reverse:
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students ALTER COLUMN phone_number DROP NOT NULL; |
Example 3: Change / remove default value
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
-- Change default enrollment year to current year + 1 for new students ALTER TABLE students ALTER COLUMN enrollment_year SET DEFAULT EXTRACT(YEAR FROM CURRENT_DATE) + 1; -- Remove default completely ALTER TABLE students ALTER COLUMN fees_paid DROP DEFAULT; |
Example 4: Multiple changes in one command (efficient)
|
0 1 2 3 4 5 6 7 8 9 10 |
ALTER TABLE students ALTER COLUMN gpa TYPE NUMERIC(4,2), ALTER COLUMN gpa SET DEFAULT 0.00, ALTER COLUMN phone_number DROP NOT NULL, ALTER COLUMN enrollment_year SET DEFAULT 2026; |
Example 5: Rename column (also ALTER TABLE, but different clause)
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students RENAME COLUMN phone_number TO mobile_number; |
(Note: this is RENAME COLUMN, not ALTER COLUMN)
3. Quick cheatsheet — most useful ALTER COLUMN actions
| What you want to do | Command example | Risk level | Notes |
|---|---|---|---|
| Change data type (safe cast) | ALTER COLUMN gpa TYPE NUMERIC(4,2) | Low | Implicit cast ok |
| Change type (needs conversion) | ALTER COLUMN year_text TYPE INTEGER USING year_text::INTEGER | High | Test with SELECT first |
| Make column NOT NULL | ALTER COLUMN email SET NOT NULL | Medium | Fill NULLs first |
| Allow NULL again | ALTER COLUMN phone DROP NOT NULL | Low | — |
| Set new DEFAULT | ALTER COLUMN status SET DEFAULT ‘pending’ | Low | Only affects new rows |
| Remove DEFAULT | ALTER COLUMN status DROP DEFAULT | Low | — |
| Multiple at once | ALTER COLUMN col1 TYPE …, ALTER COLUMN col2 SET NOT NULL | — | One statement = one lock |
4. Important warnings (from real production pain)
- Always test first → SELECT * FROM students WHERE phone_number IS NULL; before SET NOT NULL
- Large tables + type change → can lock table for minutes/hours → do it in maintenance window
- No direct way to change column order (position) — PostgreSQL doesn’t support reordering easily
- If conversion fails → use USING with safe expression (e.g. NULLIF(col, ”)::numeric)
- Backup first or use transaction: BEGIN; ALTER …; ROLLBACK; to test
Your mini practice right now
- Make gpa allow up to 10.00 → change to NUMERIC(4,2)
- Set default fees_paid to TRUE (for legacy reasons)
- Make phone_number nullable again if you set it NOT NULL earlier
- Try renaming date_of_birth to dob
Run \d students after each to see changes.
Next class?
Tell me:
- Want DROP COLUMN or DROP TABLE next?
- How to add / drop constraints (UNIQUE, CHECK, FOREIGN KEY) after table creation?
- RENAME TABLE / RENAME COLUMN deeper?
- Or back to DELETE / TRUNCATE data?
Your guru is still ON — what’s the next topic? 🚀
