PostgreSQL: Adding Columns to Tables with ALTER TABLE

Learn how to add new columns to existing PostgreSQL tables using the `ALTER TABLE ADD COLUMN` command. Includes examples and best practices.

Adding Columns to PostgreSQL Tables Using `ALTER TABLE ADD COLUMN`

The PostgreSQL `ALTER TABLE ADD COLUMN` command adds new columns to an existing table. This is a fundamental database operation for modifying your table structure and adding new data fields. This guide explains the syntax, options, and best practices for adding columns, including adding columns with constraints.

`ALTER TABLE ADD COLUMN` Syntax


ALTER TABLE table_name ADD COLUMN column_name data_type [constraints];

This command adds a new column (`column_name`) to `table_name`. `data_type` specifies the column's data type (e.g., `INTEGER`, `VARCHAR`, `DATE`). Optional `constraints` include `NOT NULL`, `UNIQUE`, `DEFAULT`, `CHECK`, etc. The new column is added at the end of the existing columns.

Adding Multiple Columns

You can add multiple columns in a single `ALTER TABLE` statement:

ALTER TABLE table_name
ADD COLUMN column1 data_type constraints,
ADD COLUMN column2 data_type constraints,
...;

    

Example 1: Adding Columns to an Empty Table

(Note: Screenshots from the original text are not included here. Please refer to the original document for visual verification of the commands' success. The descriptions below aim to convey the information present in those screenshots.)

Create a table named `Persons` with `Person_ID` and `Person_Name` columns.
Add an `Address` column using `ALTER TABLE ... ADD COLUMN`.
Add `City` and `Phone_no` columns using multiple `ADD COLUMN` clauses.

Example 2: Adding a `NOT NULL` Column to a Populated Table

Insert some rows into the `Persons` table.
Attempt to add an `Email` column with a `NOT NULL` constraint. This will fail because the new column will initially have `NULL` values, violating the constraint. You must first add the column without `NOT NULL`, update the rows to provide values for the new column, and then add the `NOT NULL` constraint.
Add the Email column without the `NOT NULL` constraint.
Update the `Email` column for existing rows.
Add the `NOT NULL` constraint to the Email column.

(Note: Screenshots from the original text are not included here. Please refer to the original document for visual verification of the commands' success.)

Follow On