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.
