# PostgreSQL - ALTER TABLE Command With Examples

> The ALTER TABLE command in PostgreSQL is used to alter the tables' structure, such as adding columns, renaming columns/tables, dropping columns, modifying cons…

In Postgres, the **ALTER TABLE** statement is used to alter/update the table’s structure. Using this command, you can easily modify the structure of any Postgres table, including adding columns, renaming columns/table, dropping columns, modifying constraints, and so on.

This blog post will discuss various use cases of Postgres’ ALTER TABLE statement via practical demonstration. So, let’s start with the basic syntax.

 **How to Alter/Modify a Table in PostgreSQL?**

Use the ADD, DROP, or RENAME keywords to perform the respective operation. For instance, the below-provided syntax is used to add, drop, or rename a column via the ALTER TABLE command:
    
    
    ALTER TABLE tab_name
    [ADD|DROP|RENAME] COLUMN col_name col_definition;

To change the column’s data type, use the ALTER TABLE command as follows:
    
    
    ALTER TABLE tab_name
    ALTER COLUMN col_name TYPE new_data_type;

Use the following syntax to add or drop a constraint using the ALTER TABLE command:
    
    
    ALTER TABLE tab_name
    ALTER COLUMN col_name SET|DROP constraint_name;

Use the ALTER TABLE command with the OWNER TO clause to change the owner of a table:
    
    
    ALTER TABLE tab_name
    OWNER TO new_owner_name;

Let’s put these concepts into practice!

 **Sample Table**

A sample table named “ **emp_data** ” has already been created. The below snippet demonstrates the content of the “ **emp_data** ” table:

Let’s modify the table’s structure via the ALTER TABLE command.

 **Example 1: Adding a New Column**

In the following **ALTER TABLE** statement, the **ADD COLUMN** clause will be used to insert a new column in the “ **emp_data** ” table:
    
    
    ALTER TABLE emp_data
    ADD COLUMN emp_age SMALLINT;

To verify the table alteration, use the “ **\d** ” command followed by the table name, i.e., “emp_data”:
    
    
    \d emp_data;

A new column named “ **emp_age** ” has been successfully inserted into the “ **emp_data** ” table.

 **Example 2: Dropping an Existing Column**

You must use the " **ALTER TABLE** " command with the " **DROP COLUMN** " clause to drop an existing column from the selected table:
    
    
    ALTER TABLE emp_data
    DROP COLUMN emp_age;

The above statement will drop the “ **emp_age** ” column from the “ **emp_data** ” table:

Let’s verify the table alteration using the “\d” command:
    
    
    \d emp_data;

The “ **emp_age** ” column from the “ **emp_data** ” table has been dropped/removed.

 **Example 3: Renaming a Column**

Use the “ **RENAME COLUMN** ” clause along with the “ **ALTER TABLE** ” command to rename a specific column of a table:
    
    
    ALTER TABLE emp_data
    RENAME COLUMN emp_joining_date TO "joining_date";

The below snippet will verify the table alteration:
    
    
    \d emp_data;

The output snippet proves that the “emp_joining_date” column has been renamed to “joining_date”.

 **Example 4: Changing Column Type**

Use the “ **ALTER TABLE** ” command with the “ **ALTER COLUMN TYPE** ” clause to change the data type of a column:
    
    
    ALTER TABLE emp_data
    ALTER COLUMN emp_id TYPE SMALLINT;

The above-provided statement will change the type of the “emp_id” column from “ **INTEGER** ” to “ **SMALLINT** ”:

Use the “ **\d** ” command to verify the column’s data type:
    
    
    \d emp_data;

The data type of the “ **emp_id** ” column has been successfully changed from “ **INT** ” to “ **SMALLINT** ”.

 **Example 5: Adding a Constraint**

To add a constraint in a specific column, you need to use the ALTER TABLE and ALTER COLUMN commands with the SET clause:
    
    
    ALTER TABLE emp_data
    ALTER COLUMN emp_id SET NOT NULL;

The above command will add a “ **NOT NULL** ” constraint in the “ **emp_id** ” column:

Let’s verify the table’s alteration using the following command:
    
    
    \d emp_data;

The “NOT NULL” constraint has been added to the emp_id column.

 **Example 6:** **Dropping a Constraint**

Suppose you want to drop a constraint from a particular column; for this purpose, you can utilize the ALTER TABLE and ALTER COLUMN commands with the DROP keyword as follows:

Let’s describe the “emp_data” table to verify the table alteration:
    
    
    \d emp_data;

The **NOT NULL** constraint has been dropped successfully from the selected column, i.e., “ **emp_id** ”.

 **Example 7: Changing Table’s Owner**

Run the “\dt” command followed by the table name to check the owner of a specific table”:
    
    
    \dt emp_data;

The above snippet shows that the owner of the “emp_data” table is “postgres”. Suppose we want to change the owner of the “ **emp_data** ” table from “ **postgres** ” to “ **cp_user** ”; for this purpose, we will use the “ **ALTER TABLE** ” command as follows:
    
    
    ALTER TABLE emp_data
    OWNER TO cp_user;

Use the “ **\dt** ” command to check the new owner of the “ **emp_data** ” table:
    
    
    \dt emp_data;

The output shows that the owner of the “emp_data” table has been changed from “ **postgres** ” to “ **cp_user** ”.

 **Example 8: Renaming a Table**

To rename a specific table in Postgres, users need to use the “ALTER TABLE” command with the “RENAME TO” clause:
    
    
    ALTER TABLE emp_data
    RENAME TO emp_info;

The above-specified statement will rename the “ **emp_data** ” table to “ **emp_info** ”:

Let’s verify the table’s name via the following command:
    
    
    \dt;

The output snippet signifies that the “ **emp_data** ” table has been successfully renamed to “ **emp_info** ”.

That’s all from this Postgres blog!

 **Conclusion**

The **ALTER TABLE** command in PostgreSQL is used to alter/update the existing tables. Users can easily modify the table’s structure using this command, such as adding columns, renaming columns/table, dropping columns, modifying constraints, etc. This blog post demonstrated the usage of the ALTER TABLE statement using relevant examples.

---
[View this page online](https://www.commandprompt.com/education/postgresql-alter-table-command-with-examples/)