Managing Table Schemas: Mastering ALTER TABLE in PostgreSQL

Previously in this course, we covered Designing the Products Table: A Practical Guide to SQL DDL to set our initial foundation. While we aim for perfect planning, real-world applications rarely stay static; business needs shift, and your database must evolve alongside them. This lesson adds the critical skill of modifying your tables after they have already been created, a process known as schema evolution.

Understanding Schema Evolution

Schema evolution is the practice of modifying your database structure to accommodate new features or changing data requirements without losing existing records. While you could technically drop a table and recreate it, that approach is destructive and impractical in production environments. Instead, we use the ALTER TABLE statement.

Think of ALTER TABLE as a surgical tool. It allows you to make precise changes to a table's structure—adding, removing, or renaming columns—while keeping the data inside intact.

Adding New Columns

As your store grows, you might need to track new information. For instance, perhaps you realize your products table needs a discount_price field to track promotional offers.

To add a column, use the ADD COLUMN clause:

SQL
ALTER TABLE products 
ADD COLUMN discount_price NUMERIC(10, 2);

When you add a column, all existing rows in that table will automatically have the value NULL for that new column, unless you specify a DEFAULT value. If you want a default, you can define it during the alteration:

SQL
ALTER TABLE products 
ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

Renaming Columns

Sometimes, a column name might no longer describe the data accurately. Perhaps you initially named a column prod_desc, but you decide that description is more readable and follows better naming conventions.

Use the RENAME COLUMN syntax to update the name without affecting the underlying data:

SQL
ALTER TABLE products 
RENAME COLUMN prod_desc TO description;

This is a metadata change. PostgreSQL updates the internal table definition, and any existing queries or applications pointing to this table will need to be updated to use the new name.

Worked Example: Updating the Store Schema

Let’s apply this to our running store project. Suppose we decide that our customers table needs to track a loyalty_points balance and that we want to rename contact_num to phone_number for clarity.

1. Add the loyalty tracking column:

   SQL
   ALTER TABLE customers 
   ADD COLUMN loyalty_points INT DEFAULT 0;
   

2. Standardize the naming convention:

   SQL
   ALTER TABLE customers 
   RENAME COLUMN contact_num TO phone_number;
   

By running these commands, you have successfully updated your schema to support new business logic without disrupting the data we inserted in previous lessons.

Hands-on Exercise

Open your psql terminal or pgAdmin query tool and perform the following steps:

1. Add a created_at column to your orders table with a data type of TIMESTAMP and a default of CURRENT_TIMESTAMP.
2. Rename the shipping_address column in your orders table to delivery_address.
3. Verify your changes by running \d orders in psql.

Common Pitfalls

• Forgetting Defaults: If you add a NOT NULL column to a table that already contains data, the operation will fail unless you provide a DEFAULT value. PostgreSQL cannot put a NULL into a NOT NULL column for existing rows.
• Application Breaking: Renaming a column is a "breaking change." If your application code (or a saved report) expects the old column name, those queries will stop working immediately. Always ensure your application code is updated to match the new schema.
• Production Risks: On very large tables (millions of rows), adding a column with a DEFAULT value can be a blocking operation. For simple projects, this isn't a concern, but it's a habit to keep in mind for future scaling.

FAQ

Can I change the data type of an existing column? Yes, you can use ALTER TABLE table_name ALTER COLUMN column_name TYPE new_type;. However, be careful, as this only works if the existing data can be successfully cast to the new type.

Is there a way to track these changes over time? Yes, professional teams use migration scripts. You can learn more about this approach in Managing Database Migrations: Versioning Your SQL Schema and Schema Versioning Basics: Managing Database Evolution for SaaS.

Recap

Schema evolution is a fundamental skill for any database practitioner. By using ALTER TABLE, you can add columns to capture new data points and rename columns to maintain clean, descriptive documentation. Remember that while these changes are powerful, they require coordination with your application code to ensure everything remains functional.

Up next: Understanding Indexes — we will explore how to make your queries significantly faster by creating B-tree indexes.