PostgreSQL

Postgres Drop Column

In PostgreSQL, you have created a table with various columns in it, and now you need to delete or drop that column from your table in the database. The column can be dropped or deleted by using the DROP COLUMN command in the ALTER TABLE statement. The DROP COLUMN clause can drop a column or more than one column from a table. This is the command that is very frequently used for database operations where the data stored is huge. For using the DROP COLUMN command in ALTER TABLE statement, first, make sure that the PostgreSQL is successfully installed on your system, and you can run queries on the server with other commands as well. In this article, you will be learning how you can remove single or multiple columns from a table in PostgreSQL with detailed explanations and different examples for your better understanding.

The basic syntax will explain how you can drop a column from a table using DROP COLUMN in the ALTER TABLE statement:

ALTER TABLE table__name
DROP COLUMN column__name ;

The above syntax of DROP COLUMN is explained as:

  • First, specify the desired table name (table__name) right after the ALTER TABLE clause that will select from the table in which you want to make the changes.
  • Then, with the DROP COLUMN clause, specify the column name (column__name) you want to drop or remove.

You have run the above syntax, and PostgreSQL displays an error that the column doesn’t exist in the database, then you can run the following command to avoid the error:

ALTER TABLE table__name
DROP COLUMN IF EXISTS column__name ;

The IF EXISTS option will remove or drop only the column that exists in the data, but if it doesn’t exist, PostgreSQL will display a notice rather than an error.

When a column is removed or dropped from a table, all the indexes and constraints associated with that column will be dropped as well. But if the column depends on other objects in the database, you can’t remove that column from the database using the above syntax. You need to use the CASCADE command in the statement to remove or drop that column from the database with all the other objects depending on it. Below is the syntax of using the CASCADE command in the statement:

ALTER TABLE table__name
DROP COLUMN column__name
CASCADE ;

The CASCADE option removes the columns, including the ones that are dependent on other database objects as well.

Now, you need to drop more than one column from the table; for that, you can follow the below syntax to drop more than one column from a table:

You will need to use commas after each column name to drop or remove more than one column from the table in PostgreSQL.

Examples to DROP COLUMN in PostgreSQL:

To drop columns in a table, we will first need to create a table in the database. The below syntax will create a table in the specified database:

CREATE TABLE PRODUCT_DETAILS(
    product_id INT PRIMARY KEY,
    product_name TEXT NOT NULL,
    product_type VARCHAR NOT NULL,
    price INT NOT NULL,
    brand_name VARCHAR NOT NULL,
    manufacture_date DATE,
    category VARCHAR
) ;

The above output verifies that the table with the name “product_details” is created. To display the table “product_details” run the following query:

SELECT * FROM "product_details" ;

This query shows the following output:

The above output ensures that the table we created above with their respective columns is done.

Now, we can drop or remove the columns in this table with different examples.

Drop Single Column from the Table in PostgreSQL:

If we want to drop one column from the table “product_details” run the following query:

ALTER TABLE "product_details"
DROP COLUMN "price" ;

The above syntax will drop the column “price” from the table “product_details” using the DROP COLUMN command in the ALTER TABLE statement, and in results will display ALTER TABLE. To verify that the table is dropped from the table, we will run this query to view the table with all columns:

SELECT * FROM "product_details" ;

This will show all the columns that exist in the table “product_details” as:

It can be seen clearly that the column “price” is now dropped from the table “product_details” and is not displayed in the table.

We know that the “price” column doesn’t exist in the table name “product_details” anymore then we can use again run the query to check if PostgreSQL shows the error message or not:

ALTER TABLE "product_details"
DROP COLUMN "price" ;

It shows the following error:

Now, if we use the IF EXISTS option in the above statement, it will show a notice instead of an error like above:

ALTER TABLE "product_details"
DROP COLUMN IF EXISTS "price" ;

Drop Multiple Columns from the Table in PostgreSQL:

We know how to drop or remove a single column from the table; dropping multiple columns requires a similar syntax, but with more than one DROP COLUMN clause with each column you want to drop. Follow the below syntax to remove more than one column from the table in PostgreSQL:

ALTER TABLE "product_details"
DROP COLUMN "brand_name" ,
DROP COLUMN "category" ;

For dropping multiple columns, I have used more than one DROP COLUMN clause to remove two columns from the table, which are “brand_name” and “category”. To check that the column is dropped, use the SELECT statement to display the table.

SELECT * FROM "product_details" ;

Only four columns are left in the table that can be seen in the above table.

Conclusion:

In this tutorial, we have talked about the DROP COLUMN clause and how you can use it to remove columns from the table in the ALTER TABLE statement in different ways. You can drop the column in the table using the DROP COLUMN clause with IF EXISTS option and the CASCADE option in PostgreSQL. For removing multiple columns from the table, you can also use more than one DROP COLUMN clause with the column names you want to remove with them.

About the author

Saeed Raza

Hello geeks! I am here to guide you about your tech-related issues. My expertise revolves around Linux, Databases & Programming. Additionally, I am practicing law in Pakistan. Cheers to all of you.