Remove Columns of a Table in PostgreSQL

Use the 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 employee table.

The following ALTER TABLE statement will remove the email column of 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 email column of 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.