# How to use the ALTER TABLE command in PostgreSQL

> In PostgreSQL, the ALTER TABLE command serves multiple functionalities on a table like adding a column, dropping a column, renaming a table/columns, etc.

In **PostgreSQL** , the **ALTER TABLE** command performs different functionalities on a table. For example, the **ALTER TABLE** statement can add, drop, or update the table columns. Moreover, it allows us to add or remove constraints to a table. So, all in all, we can say that the **ALTER TABLE** command is used to change/modify the table structure.

This write-up will explain the multiple use-cases of the **ALTER TABLE** command in **PostgreSQL.** So, let’s get started.

 **How to Add/Insert a Column in PostgreSQL?**

In **PostgreSQL** , the **ALTER TABLE** command/clause can be used to add columns to any specific table. To do so, follow the below-given syntax:
    
    
    ALTER TABLE tbl_name ADD col_name data_type;

\- Here, the **ALTER TABLE** command/statement is used to add a column in a table.

\- The **tbl-name** is the name of the targeted table.

\- **ADD** is a reserved keyword used to add/insert a column in a table.

\- **col_name** is a column name to be added to the targeted table, while **data_type** represents the column’s type.

 **Example: How to add a column with the ALTER TABLE command/statement?**

Follow the below-listed guidelines to learn how to add a new column in a table using **ALTER TABLE** :

 **Step 1: Connect to the Table**

Open the psql, and run the **“\c”** command to connect/access the desired table as shown below:
    
    
    \c example;

 **Step 2: Describe the Table**

Once you are connected to the targeted database, run the **“\d”** command to get all the necessary details about that table:
    
    
    \d team_details;

Here, **“\d”** is the command while “team_details” is the table to be described:

 **Step 3: Add a New Column**

Let’s assume you have to add a new column in the **“team_details”** table. To do so, you can run the **“ALTER TABLE”** command as follows:
    
    
    ALTER TABLE team_details ADD team_ranking int;

Here in the above snippet,

\- **ALTER TABLE** is a command.

\- **team_details** is the table name.

\- **ADD** is the reserved Keyword.

\- **team_ranking** is the column name, while **int** is the data type of that column.

So, on successful execution of the **“ALTER TABLE”** command, a new column named **“team_ranking”** of **“integer”** data type will be added to the table named **“team_details”**.

 **Step 4: Describe the Altered/Modified Table**

Let’s execute the **“\d”** command to verify that the targeted table has been altered successfully:
    
    
    \d team_details;

The above snippet verifies the working of the **“ALTER TABLE”** command.

 **How to Drop a Column in PostgreSQL?**

You have to follow the below-given syntax to drop a column using the **ALTER TABLE** command in **PostgreSQL** :
    
    
    ALTER TABLE tbl_name DROP col_name;

\- Here, the **ALTER TABLE** command/statement is used to drop a column from a table.

\- The **tbl-name** is the name of the targeted table.

\- **DROP** is a reserved keyword to drop/delete a specific column from the table.

\- **col_name** represents the column to be dropped.

 **Example: How to Use the ALTER TABLE Command to Drop a Column?**

Run the following command to drop/delete a column from any specific table:
    
    
    ALTER TABLE team_details DROP team_targets;

On successful execution of the **“ALTER TABLE”** command, a column named **“team_targets”** will be dropped from the **“team_details”** table:

Let’s verify the working of the **“ALTER TABLE”** command by executing the following command:
    
    
    \d team_details;

The output verified that the **“team_targets”** column had been dropped from the **“team_details”** table using the **“ALTER TABLE”** command.

 **How to Rename a Table in PostgreSQL?**

The **“ALTER TABLE”** command can be used along with the **“RENAME TO”** clause to rename a specific table:
    
    
    ALTER TABLE tbl_name RENAME TO modified_tbl_name;

In the above snippet,

\- **ALTER TABLE** is a command.

\- **RENAME** and **TO** are predefined clauses in **PostgreSQL** that can be used to rename a table.

\- **tbl-name** represents the table's name, while **modified_tbl_name** represents the altered/modified table name.

 **Example: How to Rename a Table using the ALTER TABLE Clause?**

Let’s execute the **“ALTER TABLE”** command to rename the **“team_details”** table to **“team_info”** :
    
    
    ALTER TABLE team_details RENAME TO team_info;

\- Here, we utilized the **“RENAME TO”** along with the **“ALTER TABLE”** command to rename a table.

\- **“team_details”** is a table name to be altered/renamed.

\- **“team_info”** represents the modified table name.

Let’s run the **“\dt”** command to get the list of relations/tables:
    
    
    \dt;

The output verified that the **“team_details”** table had been renamed to the **“team_info”**.

 **How to Rename a Column in Postgres?**

A table column in **PostgreSQL** can be renamed/modified using the **" ALTER TABLE"** command:
    
    
    ALTER TABLE tbl_name  
    RENAME old_col_name TO new_col_name;

\- Here, **ALTER TABLE** is a command used to alter a table in **PostgreSQL**.

\- **RENAME** and **TO** are predefined clauses used to rename a column.

\- **tbl_name** represents the table name.

\- **old_col_name** is a column to be altered.

\- **new_col_name** represents the altered/modified column name.

 **Example: How to Rename a Column Using ALTER TABLE Command?**

Type the below-given command to rename a column from **“team_ranking”** to **“team_rating”** :
    
    
    ALTER TABLE team_info
    RENAME team_ranking TO team_rating;

Let’s verify the column alteration using the **“\d”** command:
    
    
    \d team_info;

The output clarified that the column name **“team_ranking”** has been modified to the **“team_rating”**.

 **How to Change/Modify the Data Type of a Column in PostgreSQL?**

In **PostgreSQL** , the **“ALTER TABLE”** command can be used along with the **“ALTER COLUMN”** and **“TYPE”** clauses to modify the data type of a column **:**
    
    
    ALTER TABLE tbl_name ALTER COLUMN col_name TYPE data_type;

In the above syntax, **tbl_name** represents the table name, **col_name** is the column to be altered, and **data_type** represents the type of the column like int, varchar, etc.

 **Example: How to Use the ALTER TABLE Command to Modify/Update the Column’s Data Type?**

Let’s run the following statement to modify/alter the type of the **“team_rating”** column from **“int”** to **“char”** :
    
    
    ALTER TABLE team_info ALTER COLUMN team_rating TYPE CHAR(50);

Now compile the **“\d”** command to verify the modifications made in the **“team_info”** table:

The output verified that the column type of the **“team_rating”** has been changed from **“integer”** type to **“character”**.

 **How to ADD a PRIMARY KEY Constraint Using the ALTER TABLE Command?**

 **PostgreSQL** allows us to add or drop a constraint using the **ALTER TABLE** command, such as adding or dropping a unique constraint, primary key constraint, not null constraint, etc.

 **Example: How to Add Primary Key Constraint to a Column in PostgreSQL?**

The below snippet will let you understand how to add the **“PRIMARY KEY”** constraint in **“PostgreSQL”** using the **“ALTER TABLE”** command:
    
    
    ALTER TABLE team_info 
    ADD PRIMARY KEY (team_rating);

The output verified that multiple primary keys in the table **“team_info”** are not allowed. This is how you can add or drop any constraint in **PostgreSQL** using the **ALTER TABLE** command.

 **Conclusion**  
In **PostgreSQL** , the **ALTER TABLE** command serves multiple functionalities on a table like adding a column, dropping a column, renaming a table/columns, etc. The **ALTER TABLE** command enables us to add or drop constraints like **PRIMARY KEY** , **UNIQUE** CONSTRAINT, **NOT NULL** CONSTRAINT, etc. This write-up explained the working of the **ALTER TABLE** command with the help of suitable examples.

---
[View this page online](https://www.commandprompt.com/education/how-to-use-the-alter-table-command-in-postgresql/)