Remove Columns of a Table in PostgreSQL
ALTER TABLE DROP COLUMN statement to drop a column of a table along with existing data, indexes, and constraints created on a column.
ALTER TABLE <table_name> DROP COLUMN [IF EXISTS] <column_name> [CASCADE | RESTRICT];
Consider that you already have the following
The following ALTER TABLE statement will remove the
employee table with all its data.
ALTER TABLE employee DROP COLUMN email;
Note: If you try to remove column that does not exists, then use
IF EXISTS clause. It will remove column only if column exists, otherwise PostgreSQL will just give the warning and ignore the drop command.
Remove Dependent Objects
If a column you are trying to remove is being used in any other database objects like views, triggers, procedures, functions etc., then PostgreSQL will raise an error and will not remove a column. Use the CASCADE clause with the DROP COLUMN statement to remove a column from a table and along with dependent DB objects where it is being used.
For example, the following
employee_view view created on the
employee table, as shown bellow.
CREATE OR REPLACE VIEW employee_dtl AS SELECT * FROM employee;
Now, if you try to remove the
employee table, then PostgreSQL will raise an error, as shown below.
To remove a column which is being used in other DB objects, use the CASCADE clause with the DROP command, as shown below.
ALTER TABLE employee DROP COLUMN email CASCADE;
The CASCADE clause will remove the dependent objects too. The above statement removes the
employee_dtl view also.
Remove Multiple Columns
Use multiple DROP COLUMN clause to delete multiple columns in the table.
ALTER TABLE employee DROP COLUMN email CASCADE, DROP COLUMN gender;
Remove columns using pgAdmin
You can remove one or more columns in pgAdmin by right clicking on the table name and select 'Properties'. In the popup, go to 'Columns' tab wherein you can click on the delete icon infront of the columns to remove it, as shown below.
Click on the Save button to save the changes.