5-Minute Guide to Delete a column in PostgreSQL

Some administrative tasks in PostgreSQL databases are a necessary part of a software developer’s daily work. In a PostgreSQL database, deleting a column is a typical task. To delete a column in PostgreSQL, you can use the ALTER TABLE command with the DROP COLUMN command. The following points must be kept in mind in order to remove a column:.

1. Are there any dependencies, such as table constraints, views, or indexes, on the target column?
2. Does the target column have a reference in a foreign table?

An existing column can be deleted from a table by using the DROP COLUMN command.

DELETE Column Syntax

The syntax for dropping a column from a PostgreSQL table is shown below:

ALTER TABLE <table_name> 
DROP COLUMN <column_name>

Replace <table_name> with the table name you want to modify, and replace <column_name> with the column you want to delete from the PostgreSQL table.

Also read: Proper way to ADD COLUMN in PostgreSQL

PostgreSQL DROP COLUMN Example

ALTER TABLE employee
DROP COLUMN salary;

In the above example, I have dropped the salary column from the employee table. If any constraints exist on that table based on the target column, like unique constraints, then they will be automatically deleted.

Note: When you drop a column from the table, if any views or other foreign key dependencies exists on that column, then PostgreSQL will not allow you to delete a column from a table and show “cannot drop column…” error message.

Delete Column with all dependencies

To delete a column with all dependencies, you can use the CASCADE keyword after the DROP column command, as shown below.

ALTER TABLE employee
DROP COLUMN salary CASCADE;

Drop Multiple Columns from PostgreSQL table Example

You can drop multiple columns from a PostgreSQL table by using the same DROP COLUMN command. You need to append multiple column names after the drop column command, as shown below.

ALTER TABLE employee
DROP COLMUN age, DROP COLUMN salary;

In the above example, I have dropped two columns, age and salary, from the table of employees. Remember, if any column has any dependency, then you have to use CASCADE after the column name.

Silently DELETE Column without Error

If you try to delete a column that does not exist in that table, then PostgreSQL will throw an error saying that column “<column_name>” of relation “<table_name>” does not exist. If you want to check if column exists in table, then delete the column, then use the IF EXISTS command in PostgreSQL, like shown below

ALTER TABLE employee
DROP COLMN IF EXISTS salary;

After you put the IF EXISTS, then PostgreSQL will show a notice instead of an error saying that column “<column_name>” of relation “<table_name>” does not exist, skipping ALTER TABLE.

SUMMARY

You can easily delete or drop a column from the PostgreSQL table using the DROP COLUMN command. Remember, to remove all dependencies on the target column, you must use the CASCADE command after the DROP COLUMN command, and to silently remove the target column without error, use the IF EXISTS command.