Adding and Dropping Columns

Objectives

By the end of this lesson, you should be able to:

• Add a new column to an existing table
• Remove a column and understand what happens to its data
• Explain why a new NOT NULL column needs a DEFAULT

💡 Why this matters: A table’s requirements grow. A new feature needs a new column, an old one becomes unnecessary. This is how a table’s structure changes without losing the data already in it.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from Module 3.

Adding a Column

ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

 column_name | data_type
-------------+-------------------
 ...
 phone       | character varying

Every existing row gets NULL in the new column, since no value was given for rows that already existed. A new column can’t be NOT NULL unless it also has a DEFAULT, otherwise every existing row would instantly violate the constraint:

ALTER TABLE employees ADD COLUMN is_remote BOOLEAN NOT NULL DEFAULT false;

This works because PostgreSQL immediately fills false into is_remote for every existing row, satisfying NOT NULL for all of them from the moment the column exists.

Dropping a Column

ALTER TABLE employees DROP COLUMN phone;

This permanently deletes the column and every value stored in it, for every row. There’s no undo, if the data needs to come back, it has to come from a backup.

Try It

1. Add a hire_notes column of type TEXT to employees, confirm every existing row shows NULL in it.
2. Add an is_remote column of type BOOLEAN, NOT NULL, with a DEFAULT of false, and confirm every existing row shows false immediately.
3. Try adding a NOT NULL column with no DEFAULT to a table that already has rows, and explain the error you get.
4. Drop the hire_notes column, and confirm it’s gone from employees.

Recap

• ALTER TABLE ... ADD COLUMN name TYPE adds a new column, existing rows get NULL unless a DEFAULT is also specified.
• A new NOT NULL column needs a DEFAULT, otherwise existing rows would immediately violate it.
• ALTER TABLE ... DROP COLUMN name permanently removes a column and its data.

Next lesson: changing a column’s data type after it already exists.