Delete Column in PostgreSQL with Examples

A column can be removed from PostgreSQL once it has been created. The ALTER TABLE command can be used to delete a column. You must specify both the name of the table and the desired column alongside the alter table command to delete a column in PostgreSQL.

Syntax

The syntax for the ALTER TABLE command in PostgreSQL to delete a column is shown below.

ALTER TABLE table_name
DROP COLUMN column_name

1. table_name: Replace table_name with the table name for which you want to drop a column.
2. column_name: Replace column_name with the column you want to drop.

Delete Column Example

Suppose, we have a product tables with below columns.

CREATE TABLE products
(
	id serial primary key,
	name varchar(20),
	unit int
)

We want to remove the unit column. We shall put the table name product after the ALTER TABLE command and the column name unit after the DROP COLUMN clause. Below is an example.

ALTER TABLE products
DROP COLUMN unit

Delete Column with Dependencies (View, Foreign Keys, Triggers)

If the target column has any dependencies like views, foreign keys, or triggers, then PostgreSQL will not let you delete a column. For example, if we create a view on the products table and then try to delete a column unit, PostgreSQL will show an error: “Cannot drop a column unit of table products because other objects depend on it.” as shown below.

Now, if we want to remove a column with dependencies, we first need to remove that dependency. But PostgreSQL has provided a shortcut to remove all the dependencies for that column with a single keyword, CASCADE.

We need to put CASCADE after the column name in ALTER TABLE command.

Below is an example.

ALTER TABLE products
DROP COLUMN unit CASCADE

In the messages window, PostgreSQL gives notice that it also removes the view “vw_products” in addition to deleting a column.

Delete Multiple Columns Example

PostgreSQL allows you to delete multiple columns with a single command. You must supply multiple columns separated by commas with the DROP COLUMN clause.

Below is an example.

ALTER TABLE products
DROP COLUMN name, DROP COLUMN unit

Check Column Exists Before Delete Column

PostgreSQL will throw an error if you try to delete a column if it does not exist in the table. For example, if we try to delete a column “col1” that does not exist in the products table,. PostgreSQL will throw the error “ERROR: column “col1” of relation “products” does not exist.“

To check if the column exists before deleting a column, use the IF EXISTS clause. Below is an example.

ALTER TABLE products
DROP COLUMN IF EXISTS col1

In the Messages window, PostgreSQL will show a notice instead of an error.

Summary

In PostgreSQL, to drop a column, use the ALTER TABLE command with the DROP COLUMN clause. We can drop multiple columns in a single command with the DROP COLUMN clause, separated by commas. To check if the column exists before deleting it, use the IF EXISTS clause.