How to ALTER TABLE in PostgreSQL

In this PostgreSQL tutorial, we will discuss PostgreSQL ALTER TABLE to make changes to the properties of the table such as removing columns from the table, renaming columns and etc.

Table of Contents

PostgreSQL ALTER TABLE

The ALTER TABLE command in PostgreSQL is used to modify the properties of the table like changing the table name, column name, adding column new column to the existing table and etc.

The syntax of how to use the ALTER TABLE in PostgreSQL is given below.

ALTER TABLE table_name action;

ALTER TABLE: It is the command to make modifications to the properties of the table.
table_name: This is the name of the table on which you want to perform some modification.
action: It represents what kind of modification you want to perform on the table such as renaming the table name, or adding columns to the table or constraints to the column of the table.

Some of the action that we can perform on the table using the ALTER TABLE command is shown below examples.

PostgreSQL ALTER TABLE Adding Column

Sometimes you want to add an extra or new column to the existing table, use the action “ADD COLUMN” to add the new column along with the ALTER TABLE command.

The syntax is given below for adding a new column to the table.

ALTER TABLE table_name
ADD COLUMN column_name column_datatype;

ADD COLUMN: It is the action for adding a new column to the existing table.
column_name: The new column that will be added to the table.
column_datatype: The data type of the column means what kind of information the column is going to store such as VARCHAR, INTEGER and etc.

Suppose we have a table called “salaries” with a column ’employee_id’ and ‘salary’ containing information on employee salary in the company.

PostgreSQL ALTER TABLE Adding Column Table Salaries

Now add the new column ‘credited_date’ that stores the information of the salary credited date.

ALTER TABLE salaries
ADD COLUMN credited_date DATE;

The output contains the table with a new column named ‘credited_date’, so this is how you can add the new column to the already existing table in PostgreSQL.

PostgreSQL ALTER TABLE Renaming Table

The “RENAME TO” action is used with ALTER TABLE command to change the name of any existing table.

The syntax is given below.

ALTER TABLE table_name
RENAME TO new_tbl_name;

table_name: This is the name of the table that you want to change to a different name.
RENAME TO: It is the action used with ALTER TABLE to rename the table.
new_tbl_name: New table name that you want to keep for the existing table.

For example, suppose you want to change the table name from ‘salaries’ to a more descriptive name like ’employee_salaries’, for that use the below command.

PostgreSQL ALTER TABLE Renaming Table Salaries

In the above picture, you can see the list of tables and want to change the table name ‘salaries’ to ’employee_salaries’, then use the below command.

ALTER TABLE salaries RENAME TO employee_salaries;

The output shows the table name changed to ’employee_salaries’.

PostgreSQL ALTER TABLE Modifying Column

The “ALTER COLUM” action is used with ALTER TABLE command to modify the length, data type, default value, and constraints of the column.

The syntax is given below.

ALTER TABLE table_name
ALTER COLUMN column_name modifiction_type

ALTER COLUMN: Command to change the properties of column like data type, length and etc.
column_name: Name of the column whose properties you want to modify.
modification type: what type of modification you want to perform on the column like changing data type, constraints, or default value of the column and etc.

In one of the previous examples or topics, you added the new column ‘credited_date’ of type date to the table ‘salaries’, then in the next example changed the table name to ’employee_salaries’.

So for example this time you are going to change the newly added column ‘credited_date’ data type to a new data type named ‘timestamp’ that stores the date with time value.

ALTER TABLE employee_salaries
ALTER COLUMN credited_date TYPE TIMESTAMP;

The column ‘credited_date’ data type changed from date to timestamp as you can see in the above output.

PostgreSQL ALTER TABLE Dropping Column

The “DROP COLUMN” action is used with ALTER TABLE command to remove or delete the column permanently from any table.

The syntax is given below.

ALTER TABLE table_name
DROP COLUMN column_name;

DROP COLUMN: It is the command to delete the column from the table.
column_name: Name of the column that you want to delete.

For example, delete the column “credited_date” from the table “employee_salaries” using the below command.

ALTER TABLE employee_salaries
DROP COLUMN credited_date;

In the above output, you can see that we have successfully removed the column “credited_date” from the table “employe_salaries”.

There are lots of actions that are used with ALTER TABLE command for a specific purpose. These are the most common actions used in PostgreSQL.

Conclusion

In this PostgreSQL tutorial, we learned about the uses of action ADD COLUMN, RENAME TO, and DROP COLUM with ALTER TABLE command to modify the properties of the table.