Adding and Removing Columns in a Table

Imagine you’re designing a database for a university. At first, the students table only has two fields: ID and name. Super simple, students start using the system. But a month later, the professors come to you and say: "Hey, can you add age too?", and a bit later: "We also need email." And then: "Let’s finally get rid of the middle_name field, nobody fills it out anyway!" That’s when your ALTER TABLE skills for adding and removing columns come in handy.

Syntax for Adding Columns

So, to add a new column to an existing PostgreSQL table, you use the ALTER TABLE command. Here’s the syntax:

ALTER TABLE table_name
ADD COLUMN column_name data_type [constraints];

• table_name — the name of the table where you want to add a new column.
• column_name — the name of the column you want to add.
• data_type — the data type, like VARCHAR, INTEGER, or DATE.
• constraints (optional parameter) — constraints you want to set for this column, like NOT NULL, DEFAULT, etc.

Example: Adding a New Column

Let’s say we have a students table that was created earlier:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Suddenly, you get a task to add an age column to store students’ ages. Here’s how you do it:

ALTER TABLE students
ADD COLUMN age INTEGER;

Now the structure of the students table will look like this:

If you add new records without specifying age, PostgreSQL will automatically set the value to NULL, since we didn’t add any constraints like NOT NULL or default values (DEFAULT).

Syntax for Removing Columns

Removing a column from a table is also done with the ALTER TABLE command. Here’s the syntax:

ALTER TABLE table_name
DROP COLUMN column_name [RESTRICT | CASCADE];

• table_name — the name of the table you want to remove the column from.
• column_name — the name of the column you’re removing.
• RESTRICT — prevents column removal if there are references to it (default).
• CASCADE — removes the column along with all its dependencies.

I’ll talk more about cascading dependency removal in the next level :P

Example: Removing an Unneeded Column

Let’s go back to the students table. We decided that the age field isn’t needed anymore. Let’s remove it:

ALTER TABLE students
DROP COLUMN age;

Now the table structure is simplified again:

Handy Examples

Adding a Column with Constraints

What if you wanted to add the age column, but with a NOT NULL constraint and a default value? Here’s the code you’d use:

ALTER TABLE students
ADD COLUMN age INTEGER NOT NULL DEFAULT 18;

Now, for all existing rows, the age column will be set to 18, and new records will require a value for this field unless the default is used.

Removing a Column with Dependencies

If a column has dependencies (like it’s used in views or triggers), you’ll need to use the CASCADE modifier. But be careful: this can affect other objects in your database. For example:

ALTER TABLE students
DROP COLUMN email CASCADE;

Common Mistakes and Gotchas

Mistake: Adding a column with the wrong data type.
Imagine you add a column with type INTEGER but try to store text data in it. PostgreSQL won’t get it and will throw an error when you try to add records. The data type always has to match the content.

Mistake: Removing a column without considering dependencies.
For example, if you remove a column that’s referenced by indexes or foreign keys, PostgreSQL might throw an error if you don’t use the CASCADE modifier.

Mistake: Removing a column with dependencies. For example, if you remove a column that’s referenced by indexes or foreign keys, PostgreSQL might throw an error if you don’t use the CASCADE modifier. You added it and deleted thousands of rows that referenced your removed column. Which you didn’t need to delete : (

Gotcha: Order of operations.
When you add a NOT NULL column to a non-empty table, you either specify a DEFAULT in the same ALTER command (since PG 11+ this is instant — no table rewrite), or you add the column without NOT NULL first, backfill the values, then attach the constraint with ALTER COLUMN ... SET NOT NULL. On an empty table, ADD COLUMN ... NOT NULL without DEFAULT also works fine.

2

Task

SQL SELF, level 18, lesson 0

Locked

Adding a New Column

Adding a New Column

Open

2

Task

SQL SELF, level 18, lesson 0

Locked

Adding a column with a constraint

Adding a column with a constraint

Open