PostgreSQL ALTER TABLE

As a developer working on PostgreSQL, I have many requirements to work with the ALTER TABLE command. 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, etc.

PostgreSQL ALTER TABLE

The ALTER TABLE command in PostgreSQL is used to modify the properties of a table, such as changing the table name or column name, adding a new column to the existing table, and so on.

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 modify the table’s properties.
• 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, adding columns, or adding constraints to a column.

Below are some of the actions that we can perform on the table using the ALTER TABLE command.

Common Tasks using PostgreSQL ALTER TABLE

Case 1: Adding a new 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.

Syntax

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

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 refers to the kind of information the column will store, such as VARCHAR, INTEGER, etc.

Example

Suppose we have a table called “salaries” with columns ’employee_id’ and ‘salary’ containing information on the company’s employee 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;

After executing the above query, I got the expected output below.

The output contains the table with a new column named ‘credited_date’ as shown in the screenshot above.

Case 2: Renaming a Table

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

Syntax

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.

Example

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.

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

ALTER TABLE salaries RENAME TO employee_salaries;

After executing the above query, I got the expected output as shown in the screenshot below.

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

Case 3:Modifying an existing 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 a column, like data type, length, 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, etc.

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

Example

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’, which stores the date with a 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.

Case 4: Dropping a 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.

Example

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

ALTER TABLE employee_salaries
DROP COLUMN credited_date;

After executing the above query, I got the expected output as per the screenshot below.

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

Case 5: Adding a constraint

Below is the syntax to add a unique constraint.

Syntax

ALTER TABLE tablename ADD CONSTRAINT constraint_name UNIQUE (columnname);

Example

We can execute the following query to add a unique constraint to the existing table employee for the name column.

ALTER TABLE employee ADD CONSTRAINT constraint_name UNIQUE (name);

After executing the above query, I got the expected output as shown in the screenshot below.

Similarly, if we want to add a primary key to an existing table using the query below,

ALTER TABLE employee
ADD CONSTRAINT employee_phone_pk PRIMARY KEY (phone);

After executing the above query, I got the output as expected, as shown in the screenshot below.

Case 6: Changing NOT NULL Constraint

Example

We can modify the not null constraint using the following query.

ALTER TABLE employee 
ALTER COLUMN name 
DROP NOT NULL;

After executing the above query, I got the expected output as shown in the screenshot below.

Case 7: Dropping a Constraint

We can execute the following syntax to drop a constraint from an existing table.

Syntax

ALTER TABLE tablename DROP CONSTRAINT constraint_name;

Example

We can execute the following query to drop a constraint named employee_phone_pk from the employee table.

ALTER TABLE employee DROP CONSTRAINT employee_phone_pk;

After executing the above query, I got the expected output as shown in the screenshot below.

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 the action ADD COLUMN, RENAME TO, and DROP COLUMN with the ALTER TABLE command to modify the properties of the table.

You may like to read:

• How to Create Database in PostgreSQL
• How to Download and Install PostgreSQL on Windows
• PostgreSQL Insert Into Table Select * From Another Table
• How to Drop All Tables in PostgreSQL