Prev Next

PostgreSQL DROP COLUMN

The DROP COLUMN clause in PostgreSQL removes an existing column from a table. Use this with caution, as it permanently deletes the data contained in that column.

Key Topics

1. DROP COLUMN Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

This removes column_name from table_name. The data in that column is lost.

2. Dropping Multiple Columns

You can remove more than one column at once by separating them with commas:

ALTER TABLE employees
DROP COLUMN temp_field,
DROP COLUMN old_field;

This statement drops both temp_field and old_field columns simultaneously.

3. Using CASCADE

If other database objects (like views or foreign keys) depend on the column, you can use CASCADE to drop them automatically:

ALTER TABLE employees
DROP COLUMN old_column CASCADE;

Be sure to understand the dependencies before using CASCADE to avoid accidental drops of related objects.

Best Practices

  • Always back up or export data before dropping columns in production environments.
  • Check for dependencies, such as triggers or views, that might break if a column is removed.
  • Use CASCADE cautiously, as it can remove multiple objects.

Key Takeaways

  • DROP COLUMN is a destructive operation; data removal is permanent.
  • Multiple columns can be dropped in a single statement.
  • Understand and manage dependencies before dropping columns, especially in production.
Prev Next