In some cases, you want to modify the table structure but not the table data. In this case, you use the ALTER TABLE command. In this tutorial, we will go through all the important key points of the ALTER TABLE statement. So, let’s get started!
Also Read: PostgreSQL – A Complete Introduction, History, and Features
Introduction to PostgreSQL ALTER TABLE
The ALTER TABLE statement is used to change the table structure, i.e., you can add, delete or modify the columns of the table. Moreover, you can add, remove or modify the constraints of the table as well.
The thing worth noting is, modifying the structure of the table may or may not affect the existing table data. However, you will get errors if you try to change the column data type from one to another which is not compatible, e.g. if the column contains the string values and you are changing the type of that column to integer, PostgreSQL will not allow you to do so.
Moreover, PostgreSQL checks the new column type very strictly and won’t allow you to modify the table structure if the data can not be correctly updated to the new type.
Using the ALTER TABLE, we can:
- Add a column
- Modify a column
- Rename a column/Table
- Delete a column
- Add/Remove constraint to/from column
Now let’s see examples of each of them.
Examples of PostgreSQL ALTER TABLE
Let’s create a table first so that we can perform multiple operations on it.
CREATE TABLE employee (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
original_salary DECIMAL(10, 2),
salary_with_bonus DECIMAL(10, 2),
bonus_amount DECIMAL(10, 2)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Here we have created and inserted some records into the table. Let’s now continue to demonstrate examples of ALTER TABLE command.
You can check the table structure using the \d tableName command.
Add a New Column
To add a new column, we use the following syntax.
Syntax:
ALTER TABLE [ IF EXISTS ] tableName
ADD COLUMN [ IF NOT EXISTS ] columnName datatype [constraint];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, you can add a new column to the existing table with the optional parameter constraint. The IF NOT EXISTS clause will check if the column with the new name already exists. If it does, neither new column will be created not you will get an error.
Example:
Let’s insert the new column which will indicate the role of the employee.
ALTER TABLE employee
ADD COLUMN role VARCHAR(10);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
As you can see, we have added a new column. You can also add other constraints such as DEFAULT, NOT NULL etc. Note that, adding the NOT NULL constraint is a bit tricky. You can follow this thread to add a NOT NULL constraint to the new column.
Modify the Existing Column
Let’s now update the existing column of the employee table. Previously we created a new column role having a length of 10. But suddenly after creating the column, I thought the role name can be greater than the length of 10 characters.
Therefore, we will modify the role column and increase the length of it.
To modify the column, the following syntax is used.
Syntax:
ALTER TABLE tableName
ALTER COLUMN columnName [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the SET DATA and other parameters are optional if you want to change the column’s datatype.
Example:
ALTER TABLE employee
ALTER COLUMN role SET DATA TYPE VARCHAR(30);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
As you can see, the maximum length of the role column has been increased to 30.
Rename the Existing Column
Renaming the column and the table name is the easiest part of the ALTER COLUMN command. Let’s see the syntax.
Syntax:
-- rename the column
ALTER TABLE tableName
RENAME [COLUMN] columnName TO newName;
-- rename the table
ALTER TABLE tableName
RENAME TO newNamelCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the COLUMN keyword is optional. However, it is a good practice to mention it.
Example:
Let’s now rename the role column to job_role. To update the table name, you just have to use the RENAME clause.
ALTER TABLE employee
RENAME COLUMN role TO job_role;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
As you can see, the column name has been changed successfully.
Example 2:
Now let’s change the table name from employee to employee_data using the above syntax.
ALTER TABLE employee
RENAME TO employee_data;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
As you can see, we have successfully changed the table name.
Drop the Existing Column
You can use the ALTER TABLE command to delete the column from the table as well. Let’s see its syntax.
Syntax:
ALTER TABLE tableName
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, if you use the IF EXISTS clause, you won’t get any error even if the column that you try to delete doesn’t exist. The RESTRICT keyword will prevent dropping the column if there is any dependency present such as a foreign key.
On the other hand, if you want to delete the column any way even if it has the dependencies, use the CASCADE keyword. By default, PostgreSQL uses the RESTRICT option.
Example:
Let’s now drop the salary_with_bonus column because we don’t need it as we can calculate it and display it on the output.
ALTER TABLE employee_data
DROP COLUMN salary_with_bonus;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
As you can see, the table has been deleted successfully.
Conclusion
There are hundreds of ways and places to use the ALTER TABLE command. Covering all the syntax and examples in a single tutorial is not possible. However, this tutorial gives you a good idea to use the ALTER TABLE command. You can then follow the official PostgreSQL guide and use the commands whenever you require.
References
- PostgreSQL official documentation on the ALTER TABLE command
- Stackoverflow thread to add NOT NULL constraint while adding a new column