PostgreSQL – DROP COLUMN

You can drop a column from a table in PostgreSQL using the ALTER TABLE statement with the DROP COLUMN clause. Dropping a column will permanently remove the column and all its data from the table. Always backup your data before performing this operation.

The basic syntax to drop a column is as below:

ALTER TABLE table_name DROP COLUMN column_name;

• table_name: The name of the table from which you want to drop the column.
• column_name: The name of the column you want to drop from the table.

Below is an example of dropping a column named “obsolete_column” from a table called “my_table”:

ALTER TABLE my_table
DROP COLUMN obsolete_column;

Keep in mind the following considerations when dropping a column:

Data Loss: Dropping a column will result in the loss of all the data stored in that column. Make sure you have backed up your data before proceeding.

Dependencies: If the column you want to drop has any dependencies (e.g., foreign key constraints or triggers), PostgreSQL will not allow you to drop the column unless you resolve those dependencies. You might need to drop or modify related objects before you can drop the column.

CASCADE Option: As with dropping tables, you can use the CASCADE option to automatically drop dependent objects (e.g., indexes, rules, views) that reference the column. This can help maintain data integrity and clean up related objects along with the column.

ALTER TABLE table_name DROP COLUMN column_name CASCADE;

RESTRICT Option: The default behavior is to prevent dropping the column if there are dependencies. If you want to prevent dropping the column when there are dependent objects, you can use the RESTRICT option.

ALTER TABLE table_name DROP COLUMN column_name RESTRICT;

Dropping Multiple Columns: You can drop multiple columns in a single ALTER TABLE statement by separating their names with commas:

ALTER TABLE table_name
DROP COLUMN column1, column2, column3;

Indexes: Dropping a column will also remove any indexes defined solely on that column. If the column is part of a multi-column index, the index will still exist, but it will not include the dropped column.

Column Statistics: PostgreSQL maintains statistics about columns for query optimization. When you drop a column, the statistics for that column are also removed.