PostgreSQL ADD COLUMN

PostgreSQL ADD COLUMN (Beginner → Advanced)

The ADD COLUMN command in PostgreSQL is used to add a new column to an existing table without deleting existing data.
It’s one of the most common schema-evolution operations.

 What is ADD COLUMN?

ADD COLUMN adds a new field (column) to an existing table.

•  Table remains intact
•  Existing rows stay safe
•  New column is added to all rows

 Basic ADD COLUMN Example

Table: students

| id | name | marks |

Add a new column

 Result:

• Existing rows get NULL by default

ADD COLUMN with DEFAULT

•  New column added
•  Existing rows get 'Active' automatically
•  Future inserts also use default

ADD COLUMN with NOT NULL

 This may fail if table has rows

 Correct & Safe Way

•  Avoids constraint violation
•  Interview-friendly approach

Add Multiple Columns at Once

•  Faster
•  Cleaner migrations

ADD COLUMN IF NOT EXISTS (Safe Practice)

•  Prevents error if column already exists
•  Useful in deployment scripts

Add Column with Constraints

UNIQUE

CHECK

ADD COLUMN and Performance

• Adding a nullable column without default → very fast

• Adding a column with DEFAULT:

  • PostgreSQL 11+ → optimized (no full rewrite)

  • Older versions → table rewrite (slow)

 Best practice for large tables:

ADD COLUMN vs ALTER COLUMN

 Check Columns After Adding

 Common Mistakes

•  Adding NOT NULL without default
•  Forgetting IF NOT EXISTS
•  Adding many columns one by one
•  Running on production without testing

 Interview Questions (Must Prepare)

1. How do you add a column in PostgreSQL?

2. What happens to existing rows?

3. How to add a NOT NULL column safely?

4. Can we add multiple columns at once?

5. Difference between ADD COLUMN and ALTER COLUMN?

6. What is IF NOT EXISTS?

 Summary

• ADD COLUMN adds new fields safely
•  Existing rows get NULL or DEFAULT
•  Use IF NOT EXISTS for safe scripts
•  Combine with constraints if needed
•  Be careful with NOT NULL on large tables
• Essential for schema changes & interviews