How to use the ALTER TABLE Command in PostgreSQL with Examples

In the ever-changing world of data, your tables might need some tweaks or more significant structural changes after they’ve been created. This is where the ALTER TABLE command in PostgreSQL comes into play. This command allows you to add new columns, change data types, set default values, and even delete columns that you no longer need. In this guide, we’ll dive into how to use ALTER TABLE effectively, featuring common flags and clauses, and of course, our trusted tv_series table for examples.

What is the ALTER TABLE Command?

The ALTER TABLE command is used to change the structure of an existing table. You can use it to add, modify, or delete columns and constraints, among other things.

Basic Syntax

Here’s the general syntax for adding a new column:

ALTER TABLE table_name ADD COLUMN column_name data_type;

Making a Column Nullable

When you create a new column, it’s often a good idea to allow NULL values initially. This is especially true when you’re adding a column to an existing table that already has data. If you don’t allow NULLs, you’d have to provide a default value for all existing records, which might not be what you want.

To make a column nullable, simply don’t add the NOT NULL constraint when you define it. By default, all new columns are nullable.

Common Usage Patterns

Adding a New Column

To add a new column to the tv_series table, use the following SQL statement:

ALTER TABLE tv_series ADD COLUMN release_year INT;

In this example, we added a release_year column to the tv_series table. This column is nullable, which means it can contain NULL values. We chose to make it nullable because existing rows in the table will automatically get a NULL value in this new column, giving you the flexibility to update it later.
Here is what the table looks like now

  SELECT * from tv_series LIMIT 3;

Modifying a Column’s Data Type

If you want to change the data type of a column, you can use:

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

Deleting a Column

To remove a column entirely:

ALTER TABLE table_name DROP COLUMN column_name;

Common Errors and Solutions

Error Message:

ERROR: column "column_name" of relation "table_name" already exists

Cause:

This occurs when you try to add a column that already exists in the table.

Solution:

• Use a different column name, or check if the column already exists before attempting to add it.

Error Message:

ERROR: column "column_name" of relation "table_name" does not exist

Cause:

You’re trying to modify or delete a column that doesn’t exist in the table.

Solution:

• Double-check the column name and try again.

Error Message:

ERROR: column "column_name" contains null values

Cause:

You’re trying to alter a column to NOT NULL while it still contains NULL values.

Solution:

• Update or delete the NULL records and then try setting the column to NOT NULL again.

Now you’re ready to change your PostgreSQL tables however you see fit, all thanks to the ALTER TABLE command. Just remember, particularly with existing data, always consider the impact of your changes carefully.