In this tutorial, we will learn to delete a column from the table using the ALTER TABLE statement with the DROP COLUMN keyword. We can remove one or more columns using this statement. So, let’s see how is it done!
Introduction to PostgreSQL DROP COLUMN
In PostgreSQL, the DROP COLUMN clause is used with the ALTER TABLE statement to delete the column from the table. Deleting the column from the table is nothing but modifying the table, which needs the ALTER TABLE statement.
There are a few points you have to remember before using the DROP COLUMN statement:
- First, dropping the column results in deleting all the indexes and constraints associated with it.
- Second, if you are trying to drop the column which is referenced by another table or view or trigger or stored program, you will get an error because there is some dependency present.
These two things are enough to get started with the DROP COLUMN keyword.
Let’s now see the syntax of the DROP COLUMN.
PostgreSQL DROP COLUMN Syntax
Following is the syntax to delete a single as well as multiple columns.
-- drop a single column
ALTER TABLE IF EXISTS tableName
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ];
-- drop multiple columns
ALTER TABLE IF EXISTS tableName
DROP [ COLUMN ] [ IF EXISTS ] column_name1 [ RESTRICT | CASCADE ];
DROP [ COLUMN ] [ IF EXISTS ] column_name2 [ RESTRICT | CASCADE ];
.
.
.
DROP [ COLUMN ] [ IF EXISTS ] column_namen [ RESTRICT | CASCADE ];
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here,
- ALTER TABLE is a very important statement that is used to modify the table structure.
- IF EXISTS clause prevents the error if the table name that you specified doesn’t exit.
- DROP COLUMN statement will delete the column name you specified.
- IF EXISTS clause with the column name will prevent the error if the column doesn’t exist. This clause benefits you when you are trying to drop multiple columns and some of them don’t exist.
- RESTRICT clause will restrict the deletion of the column if there is any dependency present.
- CASCADE clause will drop the column even if there is some dependency present such as key relation or a view.
Let’s now take some examples so that you would understand the usage of the DROP COLUMN statement along with the RESTRICT and CASCADE clauses.
PostgreSQL DROP COLUMN Examples
Let’s now create a table and then demonstrate the examples of dropping the columns.
DROP TABLE IF EXISTS customers;
CREATE TABLE IF NOT EXISTS customers (
customer_id SERIAL PRIMARY KEY,
customer_unique_id INT UNIQUE,
customer_name VARCHAR(50),
customer_data VARCHAR(50)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, we have created a table ‘customers’ with few fields.
Delete a Single Column
Now, we no more need the customer_data column. Let’s delete it using the DROP COLUMN statement.
ALTER TABLE IF EXISTS customers
DROP COLUMN customer_data;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see in the output above, our column has been deleted successfully!
Now think about a situation where the parent table has a column that is referenced by the child table and you want to delete that column. What will happen? Will the PostgreSQL allows us to do so? Let’s see.
Delete a Referenced Column
We already have a table called customers. Let’s create another table called ‘orders’ and refer it to the ‘customers’ table.
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
order_data VARCHAR(50),
customer_unique_id INT,
FOREIGN KEY (customer_unique_id) REFERENCES customers(customer_unique_id)
);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)As you can see, we have created the foreign key constraint on the customer_unique_id column.
Here, the customer_unique_id column of the ‘customers’ table is a dependency for the ‘order’ table. Therefore, we will try to delete the ‘customer_unique_id’ from the ‘customers’ table.
ALTER TABLE IF EXISTS customers
DROP COLUMN IF EXISTS customer_unique_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see here, we got an error along with the error detail saying that column can not be dropped because there is a field on another table that depends on the column we are trying to delete.
The reason behind this is, there is a relationship between two tables which was established using a particular column. The second table is dependent on the first table for some sort of information, in our case- the orders table is dependent on the customers table for customer name and id. If we try to delete the column which indirectly removes the relationships, we can no longer get the information of the parent table from the child table. And to avoid this situation, PostgreSQL throws the error.
Now, what if you want to delete this column anyway?
There is a way in PostgreSQL using which we can delete the column from the parent table. Look at the below section.
CASCADE and RESTRICT Keywords
PostgreSQL gives us two keywords to use with the DROP COLUMN statement.:
- The CASCADE keyword is used when you want to delete the column of the table which is a dependency of some other table via key relation or a view.
- On the other hand, the RESTRICT keyword restricts the deletion of the column if there is some dependency present. This option is used by default with a DROP query if you don’t mention any of these two explicitly.
In the above example, we didn’t use either CASCADE or RESTRICT, therefore, the default action would be the RESTRICT. Let’s now see the CASCADE keyword in action.
In the above example, we were unable to delete the ‘customer_unique_id’ column. That we will delete using the CASCADE keyword. Let’s see.
ALTER TABLE IF EXISTS customers
DROP COLUMN IF EXISTS customer_unique_id CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, the column has been deleted successfully without any error. Now these two tables no longer have a relationship.
Conclusion
In this tutorial, we have learned about the DROP COLUMN statement which is utilized with the ALTER TABLE statement. We also learned about the CASCADE and RESTRICT keywords which are optional parameters used with the DROP COLUMN statement. You can read the official documentation if you want to learn more about this topic.