PostgreSQL ALTER table

In this section, we are going to learn the various commands of PostgreSQL ALTER TABLE for changing the structure of a table.

PostgreSQL ALTER TABLE command

We use PostgreSQL alter table command to change the current table structure.

The syntax of the alter table is given below:

The below table will show the following ALTER TABLE commands modifications:

Description	Commands
We will use the ALTER TABLE ADD COLUMN to add a new column to a table.	ALTER TABLE table_name ADD COLUMN new_column_name TYPE;
We will use the ALTER TABLE DROP COLUMN command for deleting an existing column.	ALTER TABLE table_name DROP COLUMN column_name;
For modifying the column's default value, we can use the ALTER TABLE ALTER COLUMN SET DEFAULT or DROP DEFAULT command.	ALTER TABLE table_name ALTER COLUMN column_name [SET DEFAULT value \| DROP DEFAULT];
We will use ALTER TABLE ADD CONSTRAINT command for adding a constraint.	ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;
We will use the alter table rename column to command for renaming a remaining column.	ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
For renaming a table, we will use the ALTER TABLE RENAME TO command.	ALTER TABLE table_name RENAME TO new_table_name;
For adding the CHECK constraint, we will use the ALTER TABLE, ADD CHECK command.	ALTER TABLE table_name ADD CHECK expression;
To change the NOT NULL constraint, we will then use ALTER TABLE ALTER COLUMN command.	ALTER TABLE table_name ALTER COLUMN column_name [SET NOT NULL\| DROP NOT NULL];

In PostgreSQL, we can perform various activities in the alter table command, which is as follows:

Add columns
Rename a column
Drop a column
Add a CHECK constraint to a column
Modify a column's data type
Set a default value for the column
Rename a table

Examples of PostgreSQL ALTER TABLE

For our better understanding of the ALTER TABLE command, we will create a new table named Station with the help of Create table command:

CREATE TABLE Station (
St_id INTEGER PRIMARY KEY,
St_Name Varchar(200),
St_City CHAR(30),
St_State CHAR(2)
);

Output

After executing the above command, we will get the below message window:

Once we have done creating a Station table, we will perform the following actions on that particular table:

Add a column

After that we will add a new column named Latitude with the help of below command:

ALTER TABLE Station
ADD COLUMN Latitude REAL;

Output

Once we execute the above command, we will get the below message window:

Drop a column

To delete the Latitude column from the Station table, we will use the below command:

ALTER TABLE Station
DROP COLUMN Latitude;

Output

We will get the below message after executing the above statement:

Rename a column

To rename the St_Name column to Name, we use the below command:

ALTER TABLE Station
RENAME COLUMN St_Name TO Name;

Output

Once we implement the above command, we will get the below message:

We use the SELECT command to check all the above alter operation:

Output

We will get the below output, after running the above command:

Rename a table

To rename the Station table to Station1, we will use the below command:

ALTER TABLE Station
RENAME TO  Station1;

Output

Once we perform the above command, we will get the below message:

Change column type

The below command is used to modify the type of St_City and St_State columns from Char to Varchar in Station1 table:

ALTER TABLE Station1
ALTER COLUMN St_City TYPE Varchar(30),
ALTER COLUMN St_State TYPE Varchar(20);

Output

After executing the above command, we will get the below message:

Add NOT NULL constraint

If we want to add the NOT NULL constraint to St_City column in Station1 table, we will use the below command:

Output

Once we execute the above command, we will get the below message:

Remove NOT NULL constraint

If we want to delete the NOT NULL constraint from the St_City column in the Station1 table, we will run the below command:

ALTER TABLE Station1 
ALTER COLUMN 
St_City DROP NOT NULL;

Output

After executing the above command, we will get the below message:

Add PRIMARY KEY constraint9

If we want to add a primary key constraint in the Station1 table, we will use the below command.

Output

In the below message window, only one primary key is allowed for a table. Therefore, we will assume that there is no pre-set primary key in the table.

SUMMARY

We can drop the Columns with the help of an alter command. And if indexes or any table constraints linked, then the related column will automatically be dropped. Or, for another table, we can use the Cascade if the table is referenced.
By using alter table command, we can add the Columns as well as Constraints in a particular table.
We can also delete the default value for a column with the help of an alter command.
We can modify the columns' data type, and the connected indexes and constraints will be automatically modified to the new column type