In PostgreSQL, the DROP COLUMN command is used to drop an individual column or multiple columns. The DROP COLUMN statement in PostgreSQL is used with the collaboration of the ALTER TABLE command. In PostgreSQL, some options like “CASCADE” and “IF NOT EXISTS” can be used with the DROP COLUMN command to achieve different functionalities.

How to Drop/Delete a Single Column from a Table in PostgreSQL?

The syntax given below is used to drop a single column from a table in PostgreSQL:

ALTER TABLE tbl_name  
DROP COLUMN col_name;

Here, ALTER TABLE is a command, and tbl_name represents a table to be modified. The DROP COLUMN is a command, and col_name represents a column to be dropped.

Example# 1: How to Drop/delete a Single Column in Postgres?

Let’s follow the below-given guidelines to get a profound understanding of the DROP COLUMN command.

Step1: Select a Table

Open the pgAdmin, and select a table:

From the available tables, we select the “student_details” table.

Step 2: Drop the Column

Select a column that you want to drop from the targeted table:

Let’s say you have to drop the “student_age” column. To do so, execute the following command:

ALTER TABLE student_details  
DROP COLUMN student_age;

Step 3: Verify the Table Alteration

Let’s refresh the targeted table and then click on the “Columns” section to see the available columns in the selected table:

The above snippet shows that the “student_age” column has been dropped successfully.

Example# 2: Drop a Column That Does Not Exist

In PostgreSQL, if someone tries to drop a column that does not exist, then an error will occur:

ALTER TABLE student_details  
DROP COLUMN student_age;

The above snippet proved that an error occurred when we tried to drop a column that didn’t exist.

Example# 3: How to Avoid the “Relation Doesn’t Exist” Error?

Use the “IF EXISTS” parameter along with the “DROP COLUMN” command to avoid such an error:

ALTER TABLE student_details  
DROP COLUMN IF EXISTS student_age;

This time, the query returned successfully, and the “DROP COLUMN” command shows a notice instead of throwing an error.

How to Drop/Delete Multiple Columns From a Table in PostgreSQL?

In Postgres, the DROP COLUMN command can be used with the comma-separated syntax to drop more than one column:

ALTER TABLE tbl_name  
DROP COLUMN col1,

DROP COLUMN col2,
DROP COLUMN col3;

In this way, you can drop as many columns as you want simultaneously.

Example: How to Delete/Drop Multiple Columns in Postgres?

Follow the below-given procedure to delete the multiple columns simultaneously:

Step 1: Drop the Unwanted Columns

Let’s execute the following query to drop the “student_roll” and “student_name” columns from the “student_details” table:

ALTER TABLE student_details  
DROP COLUMN student_roll,
DROP COLUMN student_name;

Step 2: Verify the Table Alteration

Let’s execute the following command from the SQL SHELL to verify the modifications made in the “student_details” table:

\d student_details;

The above snippet proved that the selected columns had been dropped successfully.

What is CASCADE and How to Use it in PostgreSQL?

In PostgreSQL, attempting to delete a column on which other columns/objects depend will cause an error. We can use the “CASCADE” option along with the “DROP COLUMN” command to drop/delete the targeted column with all the associated objects. Following will be the basic syntax of the “CASCADE” option:

ALTER TABLE tbl_name
DROP COLUMN col_name CASCADE;

How to Get the Structure of a Table in PostgreSQL?

Let’s execute the “\d” command from the SQL SHELL(psql) to get the details of the “author_details” table:

\d author_details;

The above snippet shows that the “author_id” is used as a foreign key in the “article_details” table. This means the “article_details”table depends on the “author_id” column of the “author_details” table.

Example# 1: “Can’t drop a column” Error in Postgres

Run the following query to drop the “author_id” column of the “author_details” table:

ALTER TABLE author_details  
DROP COLUMN author_id;

The output shows that we encountered an error “cannot drop column author_id”. This is because some other objects depend on the “author_id” column.

Example# 2: How to Drop/Delete the Dependent Columns?

Let’s execute the following query to drop the “author_id” column and the objects associated with that column:

ALTER TABLE author_details
DROP COLUMN author_id CASCADE;

The output clarified that the “author_details” table has been altered successfully.

Conclusion

In PostgreSQL, the DROP COLUMN statement is used with the collaboration of ALTER TABLE clause to drop/delete the columns. The DROP COLUMN command can accept some options such as “CASCADE” and “IF NOT EXISTS”. These options facilitate us with different functionalities. This write-up explained the working of the DROP COLUMN command with the help of examples.