Chapter 9: PostgreSQL ADD COLUMN
PostgreSQL ADD COLUMN ☕🛠️
You’ve already:
- Created tables (CREATE TABLE)
- Inserted data (INSERT)
- Queried data (SELECT)
Now your app or project evolves → you realize “Oh no, I forgot to store the student’s phone number / profile picture URL / enrollment status / last login time…”
That’s exactly when you use ADD COLUMN.
1. What does ADD COLUMN really do? (Teacher’s honest explanation)
ADD COLUMN is a sub-clause of the ALTER TABLE command. It tells PostgreSQL:
“Please add one (or more) new column(s) to an existing table. The new column(s) will appear at the end of the table definition (you cannot choose position easily). For all existing rows, the new column will be filled with NULL — unless you give it a DEFAULT value or use other tricks.”
Key points in 2026 (PostgreSQL 18.x):
- Very safe and fast on small-to-medium tables
- On very large tables (hundreds of millions of rows) → can take noticeable time because PostgreSQL needs to rewrite the table metadata (but no full table rewrite anymore for simple ADD COLUMN since PostgreSQL 11+)
- You can add constraints (NOT NULL, CHECK, DEFAULT, UNIQUE, etc.) at the same time
- IF NOT EXISTS option (very useful in migrations/scripts to avoid errors)
Official syntax (from current docs — PostgreSQL 18):
|
0 1 2 3 4 5 6 7 8 9 10 |
ALTER TABLE table_name ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] [, ...]; |
You can add multiple columns in one command (comma-separated).
2. Real example — evolving our students table
Assume we have this students table (from earlier classes) with some data already inserted:
|
0 1 2 3 4 5 6 |
\d students |
Current columns:
- id (BIGSERIAL PRIMARY KEY)
- first_name (VARCHAR(50) NOT NULL)
- last_name (VARCHAR(50) NOT NULL)
- email (VARCHAR(100) UNIQUE NOT NULL)
- date_of_birth (DATE)
- gpa (NUMERIC(3,2))
- is_active (BOOLEAN DEFAULT TRUE)
- created_at (TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP)
- address (JSONB)
Now the college wants to track:
- Phone number
- Profile picture URL
- Whether the student has paid fees (with default false)
- Enrollment year (NOT NULL, must provide value)
Let’s add them:
Basic — add one simple column
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students ADD COLUMN phone_number VARCHAR(15); |
→ After this, all existing rows get phone_number = NULL → New inserts can have phone or leave it NULL
Add column with DEFAULT value
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students ADD COLUMN fees_paid BOOLEAN DEFAULT FALSE; |
→ Existing rows → fees_paid = FALSE (great for migration!) → New rows → auto FALSE unless you say otherwise
Add column with NOT NULL + DEFAULT (safe way)
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students ADD COLUMN enrollment_year SMALLINT DEFAULT EXTRACT(YEAR FROM CURRENT_DATE) NOT NULL; |
→ Why this works: DEFAULT fills existing rows → then NOT NULL is satisfied → If you try NOT NULL without DEFAULT → error on existing rows!
Add multiple columns in one command (very common in migrations)
|
0 1 2 3 4 5 6 7 8 9 |
ALTER TABLE students ADD COLUMN profile_pic_url VARCHAR(255), ADD COLUMN whatsapp_number VARCHAR(15), ADD IF NOT EXISTS last_login TIMESTAMPTZ; |
→ IF NOT EXISTS → safe to run multiple times (no error if column already there)
Add column with CHECK constraint
|
0 1 2 3 4 5 6 7 8 |
ALTER TABLE students ADD COLUMN blood_group VARCHAR(5) CHECK (blood_group IN ('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-')); |
Add UNIQUE constraint on new column
|
0 1 2 3 4 5 6 7 |
ALTER TABLE students ADD COLUMN university_roll_no VARCHAR(20) UNIQUE; |
3. Important rules & behaviors (2026 reality)
| Scenario | What happens to existing rows? | Time taken (approx) | Tip / Gotcha |
|---|---|---|---|
| Simple ADD COLUMN (no DEFAULT) | NULL | Very fast | Common |
| ADD COLUMN … DEFAULT value | Filled with that default | Fast | Preferred for flags/dates |
| ADD COLUMN … NOT NULL without DEFAULT | Error — cannot satisfy NOT NULL | — | Always add DEFAULT first if NOT NULL needed |
| ADD COLUMN … DEFAULT + NOT NULL | Filled with default → constraint ok | Fast | Safe migration pattern |
| ADD COLUMN on table with 100M+ rows | Still fast (metadata change only) | Seconds to minutes | Since PG 11+ no full rewrite |
| ADD COLUMN + FOREIGN KEY constraint | Allowed, but existing rows must satisfy (NULL ok unless NOT NULL) | Fast | Add FK after data if needed |
4. Verify it worked (psql or pgAdmin)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- See updated structure \d students -- See new columns on existing rows SELECT first_name, last_name, phone_number, fees_paid, enrollment_year FROM students LIMIT 5; -- In pgAdmin: right-click table → Properties → Columns tab |
5. Common real-world patterns & mistakes
Pattern 1: Safe migration (add nullable first, backfill, then NOT NULL)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Step 1 ALTER TABLE students ADD COLUMN emergency_contact VARCHAR(100); -- Step 2: Backfill data (in batches if huge table) UPDATE students SET emergency_contact = 'N/A' WHERE emergency_contact IS NULL; -- Step 3 ALTER TABLE students ALTER COLUMN emergency_contact SET NOT NULL; |
Pattern 2: Add generated column
|
0 1 2 3 4 5 6 7 8 |
ALTER TABLE students ADD COLUMN full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED; |
Mistake to avoid
|
0 1 2 3 4 5 6 7 8 |
-- Wrong: will fail if table has rows ALTER TABLE students ADD COLUMN status VARCHAR(20) NOT NULL; -- Error: column "status" contains null values |
Fix: Add DEFAULT ‘pending’ or make it nullable first.
Your mini homework right now
- Add these columns to your students table:
- phone_number VARCHAR(15)
- is_hosteller BOOLEAN DEFAULT FALSE
- admission_date DATE DEFAULT CURRENT_DATE NOT NULL
- Run SELECT * FROM students LIMIT 3; — see how defaults appear
- Try adding a column that already exists with IF NOT EXISTS — see it doesn’t error
Next topic?
Tell me:
- Want ALTER TABLE … ALTER COLUMN (change type, drop NOT NULL, set default…)?
- How to DROP COLUMN safely?
- UPDATE existing rows to fill new columns?
- Or move to constraints (ADD CONSTRAINT, foreign keys on existing table)?
Your teacher is ready — just say the word! 🚀
