The fields of data are advancing rapidly, so there might be some data attributes that were useful to us previously but have become unnecessary and outdated over time. It is better to delete such data attributes to avoid unnecessary data and only have clean data for good understanding, efficient analysis, and performance of data. In PostgreSQL, we can simply drop the columns that are of no use to us. This article will cover the ways we can drop a column from a table in PostgreSQL.
How to Drop/Delete Columns From a Table in PostgreSQL?
We can delete columns from a table in PostgreSQL. This can be accomplished by two approaches:
Let’s see how we can implement both methods to delete columns from a table.
Method 1: Drop Columns From The Table Using Postgres Queries
We can use the ALTER statement to drop columns from the table. The basic query for this purpose will be:
ALTER TABLE t_name
DROP COLUMN c_name;
Syntax Description
The syntax for dropping a column from the table is quite simple:
- The ALTER TABLE alters the table.
- The table name that we wish to alter is specified after the ALTER TABLE statement.
- The DROP COLUMN clause is used to drop a Postgres column of a table.
- The column’s name that is to be dropped is specified after the DROP COLUMN clause.
Let’s see how we can implement the above syntax to drop columns.
Example 1: Drop Columns From the Table Using Postgres Queries
Let’s consider the table “account details” with 4 columns:
Now we will drop/delete a column from the above table.
Step 1: Drop the Table Using the ALTER Statement
Let’s drop the “address” column. The query for this will be:
ALTER TABLE account_details
DROP COLUMN address;
The above query will delete the address column from the table. The query outputs:
Now we need to verify if the column has been deleted or not.
Step 2: Verify if the Column has Been Dropped
We can verify this in several ways. Let’s execute the SELECT statement to verify the removal of the selected column:
SELECT * FROM account_details;
The output of the SELECT query is:
In the above output, we can see that the “address” column, which was deleted, is not present. Which ensures the deletion of the table. The same thing can also be verified from the pgAdmin’s side panel.
We can also delete multiple columns from a table with the same method. The basic syntax can be written as:
ALTER TABLE t_name
DROP COLUMN c1_name
DROP COLUMN c2_name;
Where c1_name and c2_name are the column names that we desire to delete.
Example 2: Drop Columns that Do Not Exist in the Table
If we try to drop a column that does not exist with the same syntax given above, i.e. ALTER TABLE and DROP COLUMN, the query will throw an error. Execute the following query for demonstration:
ALTER TABLE account_details
DROP COLUMN country;
The query will throw an error that the “country” column does not exist in the “account_details” table like this:
To avoid this error, DROP COLUMN IF EXISTS is used. The DROP COLUMN IF EXISTS statement drops\deletes the column if it exists. If not, it will not throw an error instead will show a notice and skip the statement. So we will write the following query:
ALTER TABLE account_details
DROP COLUMN IF EXISTS country;
We can see that the query has not thrown an error, but raised the notice and skipped the statement.
Forcibly Drop Columns From The Table Using Postgres Queries
There are some cases when we want to delete/drop a column but that column is already using some fields of other database objects such as a table. The syntax to drop a column, in this case, can be written as:
ALTER TABLE t_name
DROP COLUMN c_name CASCADE;
Adding CASCADE at the end of the query will forcibly drop/delete a column.
For example, if we have “address” as a foreign key in the above-considered table. This foreign key is being used by some other table ”fee_invoice”. This means that the “address” table behaves as the parent for the “fee_invoice” table.
The “fee_invoice” table is referred to by the “account_details”, as the foreign key “address” of “account_details” is used by the “fee_invoice”. So when we want to delete the column “address” from the table ”fee_invoice”, it will raise an error as “account_details” is being used by the “fee_invoice”. The query to drop the column from fee_invoice is:
To drop this we will have to forcibly delete the address column using cascade. We will write the following query for that:
ALTER TABLE fee_invoice
DROP COLUMN address CASCADE;
This will alter the table to drop the “address” column. We will verify the table columns by executing the select query:
We can clearly see that the column has been dropped.
This is how we can drop/delete a column from a table in PostgreSQL using Postgres queries. There is another way to drop columns.
Method 2: Drop Columns From the Table Manually Using pgAdmin
We can also manually drop columns from a table in pgAdmin. To do this follow the steps given below:
Step 1: Delete the Table Column Manually
Go to the column of the table you want to delete. Right-click on the name of that column and choose the “delete/drop” option.
This will open a window for the confirmation to delete the row. Press “Yes” to delete the column.
Step 2:Verify the Deleted column
You can verify the deleted column from the side pane. Otherwise, you can also execute the SELECT query to do this:
You can see that the “Passcode” column is no longer available and is deleted.
In this way, we can drop/delete columns from a table.
Conclusion
To drop/delete a column from a table, we can use the ALTER TABLE and the DROP COLUMN statement together. The ALTER TABLE statement alters the table and the DROP COLUMN statement will drop the specified table. To forcibly drop a column we will have to add a CASCADE keyword at the end of the query. We can also manually drop/delete a column in pgAdmin by following simple steps. In this blog, we have learned how we can drop/delete columns from a table in PostgreSQL.
