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, UNIQUECONSTRAINT, NOT NULL CONSTRAINT, etc. This write-up explained the working of the ALTER TABLE command with the help of suitable examples.
