Changing Table Structure with ALTER TABLE

Now it's time to talk about how to change tables that already exist. In real project life, almost nothing stays the same—requirements can drop in out of nowhere, like a bombardilokrokodilo. For example, your product manager suddenly decides every user needs a unique ID for their favorite profile background color. Or you realize that VARCHAR(50) for a name is just not enough, since you already have a user named "Bombardilokrokodilo-Junior-Third" in your database.

Well, PostgreSQL has a tool for this kind of thing—the ALTER TABLE command.

ALTER TABLE is a command that lets you change the structure of existing tables. With it, you can:

• Add new columns;
• Remove unnecessary columns;
• Change a column's data type;
• Rename a table or its columns;
• Add or remove constraints like NOT NULL, UNIQUE, and a bunch more.

Think of it like this: you've already built a house (the table), but now you want to add a new room (a column) or get rid of a useless closet (drop a column).

Syntax of ALTER TABLE

The general structure of the command is pretty intuitive:

ALTER TABLE table_name
    change_action;

Where:

• table_name — the name of the table you want to change;
• change_action — the specific action you want to perform (like adding a column, dropping it, or changing its data type).

Examples of Using ALTER TABLE

Adding a New Column

If you want to add a new column to a table, use the ADD COLUMN command. Here's an example:

ALTER TABLE students
ADD COLUMN email VARCHAR(100);

This command adds a new email column to the students table, where you'll store the student's email address. The column's data type is a string up to 100 characters long.

Let's say you created a students table but forgot to add info about their enrollment date. Here's how to fix that:

ALTER TABLE students
ADD COLUMN enrollment_date DATE;

Now you've got an enrollment_date column, and you can save the enrollment date.

Dropping a Column

If you decide a column isn't needed anymore, use the DROP COLUMN command. For example:

ALTER TABLE students
DROP COLUMN email;

This command deletes the email column from the students table. Keep in mind, the data in this column will also be deleted with no way to get it back.

Important note:

If you try to drop a column that's used in other tables (like as a foreign key), PostgreSQL might throw an error. In those cases, you need to remove dependencies first.

Changing a Column's Data Type

Sometimes the data type you picked at first just doesn't cut it. For example, you decided to store students' ages as SMALLINT, but then realized that's not enough, and you want to use BIGINT. Here's how you do it:

ALTER TABLE students
ALTER COLUMN age TYPE BIGINT;

Important note: if you've already got data in the column, PostgreSQL checks if it can be converted to the new type. If not, you'll get an error.

Let's say you created a description column as VARCHAR(100), but then realized course descriptions can be way longer. Change the data type to TEXT:

ALTER TABLE courses
ALTER COLUMN description TYPE TEXT;

Now you can store full course descriptions with no length limits.

Renaming a Column

If you need to change a column's name, use the RENAME COLUMN command:

ALTER TABLE students
RENAME COLUMN name TO full_name;

Now the name column will be called full_name.

Renaming a Table

You can also change the table's name itself. For that, use the RENAME TO command:

ALTER TABLE students
RENAME TO university_students;

Now the students table will be called university_students.

Setting/Removing the NOT NULL Constraint

Let's say you created a column but forgot to make it required (NOT NULL). No worries! You can add this constraint later:

ALTER TABLE students
ALTER COLUMN name SET NOT NULL;

If you want to remove the NOT NULL constraint, use the DROP NOT NULL command:

ALTER TABLE students
ALTER COLUMN name DROP NOT NULL;

Setting a Default Value

If a column already exists but you want to set a default value for it, use SET DEFAULT:

ALTER TABLE enrollments
ALTER COLUMN enrollment_date SET DEFAULT CURRENT_DATE;

To remove the default value, do this:

ALTER TABLE enrollments
ALTER COLUMN enrollment_date DROP DEFAULT;

We'll go into more detail on each command in the next three lectures. So get ready :)

Common Mistakes When Using ALTER TABLE

You might run into errors when working with ALTER TABLE. Here are the most common ones:

Trying to change to a data type that's not compatible with the current data. For example, if the age column already has data and you try to convert it to a VARCHAR string, you'll get an error. Solution: delete or convert the data first.

Trying to drop a column that's used as a foreign key or index. PostgreSQL won't let you do this to keep your data safe. Before dropping dependent columns, you need to remove the relationships.

Syntax errors. The ALTER TABLE command is pretty flexible, but you have to get the syntax exactly right. For example, the ADD COLUMN command has to include the data type.

Real-Life Use Cases

In real life, the ALTER TABLE command gets used all the time. You might see it when:

• Adding new data to an existing table (like new columns);
• Optimizing your database (like changing data types to save memory);
• Fixing design mistakes (like if you forgot about constraints or picked the wrong data type at first).

Now you get how powerful the ALTER TABLE command is. It's a strong tool that helps you handle changes in your project. The main thing—be careful and don't rush, so you don't accidentally "break the house" (your table) while trying to add a new room.