TutorialsArena

PostgreSQL: Dropping Columns with ALTER TABLE DROP COLUMN

Safely remove columns from PostgreSQL tables using ALTER TABLE DROP COLUMN. This guide explains the command and its implications.

Dropping Columns in PostgreSQL Using `ALTER TABLE DROP COLUMN`

In PostgreSQL, you can remove columns from a table using the `ALTER TABLE DROP COLUMN` command. This operation permanently deletes the specified column and its associated data. It's crucial to understand the implications, especially when the column is referenced by other database objects like views or foreign key constraints.

`ALTER TABLE DROP COLUMN` Syntax


ALTER TABLE table_name DROP COLUMN column_name;

This command removes the specified `column_name` from the `table_name` table. PostgreSQL automatically removes any constraints or indexes associated with that column. Multiple columns can be dropped using multiple `DROP COLUMN` statements separated by commas, or using multiple `ALTER TABLE` statements.

Dropping Columns with Dependencies

If a column is referenced by other database objects (like foreign key constraints, views, triggers, or stored procedures), attempting to drop it directly will result in an error. In such cases, you use the `CASCADE` option to also drop the dependent objects. Otherwise, you'll need to remove those dependencies manually before dropping the column. 


ALTER TABLE table_name DROP COLUMN column_name CASCADE;

Dropping Non-Existent Columns

Attempting to drop a non-existent column will usually cause an error. To avoid this, use the `IF EXISTS` clause. This will result in a notice instead of an error if the column doesn't exist.


ALTER TABLE table_name DROP COLUMN IF EXISTS column_name;

Example: Dropping Columns in a Database

(Note: This example involves creating tables and a view. Screenshots from the original text are not included here. Please refer to the original document for visual confirmation of the commands' success. The descriptions below aim to convey the information present in those screenshots.)

  1. Create Tables: Create `Categories`, `Product`, and `Brand` tables.
  2. Create View: Create a view `Product_info` based on `Product` and `Brand`.
  3. Drop a Column: Drop the `cate_id` column from the `Product` table (it's not referenced elsewhere).
  4. Drop a Referenced Column: Attempt to drop the `Brand_id` column from `Product`. This will fail because the column is used in the `Product_info` view. Use `CASCADE` to drop the dependent view.
  5. Drop Multiple Columns: Drop the `list_price` and `model_year` columns using multiple `DROP COLUMN` statements.