Database Essentials using Postgres - Creating Tables and Indexes - Adding or Modifying Columns

In this article, we will delve into the details of adding or modifying columns using the ALTER TABLE command in SQL. We will understand how new columns can be added, existing columns can be modified or dropped, and how to perform these operations safely and effectively.

Key Concepts Explanation

Adding a New Column

When adding a new column to an existing table, follow these steps:

  1. Add the new column to the table.
  2. Update the data in the column with some initial value.
  3. Alter the table to enforce any constraints, such as making the column not nullable.

Modifying an Existing Column

Existing columns can be modified to change data types or define constraints. For example, changing a column to not null or altering its data type. Be cautious when modifying columns in a production environment.

Dropping a Column

While it is possible to drop columns from a table, it is not advisable, as it can break application functionality. Exercise caution and consider the impact on existing applications before dropping a column.

Hands-On Tasks

  1. Change the data type of user_id to SERIAL and set it as the default value.
  2. Define default values for user_email_validated and is_active as FALSE.
  3. Change the data type of user_role to CHAR(1) and set the default value to 'U'.
  4. Add a new column last_updated_ts with a data type of TIMESTAMP and set the default value to the current timestamp.


In this article, we have explored the concepts of adding, modifying, and dropping columns in SQL using the ALTER TABLE command. By following the provided tasks, readers can practice and enhance their understanding of these operations. Remember to exercise caution, especially in production environments, and always consider the impact of column-level modifications on existing applications. Happy coding!

Click here to watch the full video tutorial on adding or modifying columns in SQL.

Watch the video tutorial here