PostgreSQL Drop Column

Summary: In this tutorial, you will learn how to drop a column from a table using the PostgreSQL ALTER TABLE DROP COLUMN statement.

Getting Started with the PostgreSQL ALTER TABLE DROP COLUMN Statement #

When a column is obsolete, removing it from the table is necessary to avoid storage overhead and improve the database performance.

In PostgreSQL, you can use the ALTER TABLE DROP COLUMN statement to remove a column permanently from a table.

ALTER TABLE table_name
DROP COLUMN column_name;

In this syntax:

First, specify the table name you want to drop the column in the ALTER TABLE clause.
Second, provide the column name you want to drop in the DROP COLUMN clause.

If you remove a column that does not exist, PostgreSQL will issue an error. To avoid the error, you can use the IF EXISTS option:

ALTER TABLE table_name
DROP COLUMN IF EXISTS column_name;

In this case, PostgreSQL will issue a notice instead of an error if the column does not exist. This notice can be helpful when you need to know if the column exists and handle it properly from your application.

When you drop a column, PostgreSQL will delete the data from that column. It will also automatically remove the table constraints and indexes involving the column.

If you remove a column referenced by other objects outside the table, such as views and foreign key constraints, PostgreSQL will also return an error. In this case, you can use the CASCADE option:

ALTER TABLE table_name
DROP COLUMN IF EXISTS column_name CASCADE;

The CASCADE option will drop the column and its dependent objects, such as foreign key constraints and views, in one go.

To drop multiple columns at once, you can use multiple DROP COLUMN clauses:

ALTER TABLE table_name
DROP COLUMN column1,
DROP COLUMN column2,
DROP COLUMN column3;

Dropping One Column Example #

First, create a tablesuppliers that stores suppliers information, including name, phone, email, fax, and address:

CREATE TABLE suppliers (
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   phone VARCHAR(25) NOT NULL UNIQUE,
   email VARCHAR(255) NOT NULL UNIQUE,
   fax VARCHAR(25) NOT NULL UNIQUE,
   address TEXT,
   notes TEXT
);

Second, drop the notes column from the suppliers table.

ALTER TABLE suppliers 
DROP COLUMN notes;

Third, verify the column removal:

d suppliers

Output:

                                Table "public.suppliers"
 Column  |          Type          | Collation | Nullable |           Default
---------+------------------------+-----------+----------+------------------------------
 id      | integer                |           | not null | generated always as identity
 name    | character varying(255) |           | not null |
 phone   | character varying(25)  |           | not null |
 email   | character varying(255) |           | not null |
 fax     | character varying(25)  |           | not null |
 address | text                   |           |          |
Indexes:
    "suppliers_pkey" PRIMARY KEY, btree (id)
    "suppliers_email_key" UNIQUE CONSTRAINT, btree (email)
    "suppliers_fax_key" UNIQUE CONSTRAINT, btree (fax)
    "suppliers_phone_key" UNIQUE CONSTRAINT, btree (phone)

The output indicates that the statement has successfully removed the notes column.

Dropping a Column with Dependent Objects #

First, create a table called fax_messages to store fax messages:

CREATE TABLE fax_messages (
     id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
     fax VARCHAR(255) NOT NULL,
     message TEXT NOT NULL,
     sent_at TIMESTAMPTZ NOT NULL,
     FOREIGN KEY (fax) REFERENCES suppliers(fax) ON DELETE CASCADE
);

The fax_messages table has the fax column as a foreign key constraint that references the fax column of the suppliers table.

Second, create a view called faxes based on the suppliers table:

CREATE VIEW faxes AS
SELECT name, fax
FROM suppliers;

Third, drop the fax column from the suppliers table:

ALTER TABLE suppliers 
DROP COLUMN fax;

Error:

ERROR:  cannot drop column fax of table suppliers because other objects depend on it
DETAIL:  constraint fax_messages_fax_fkey on table fax_messages depends on column fax of table suppliers
view faxes depends on column fax of table suppliers
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

PostgreSQL issued an error because the fax table has the following dependent objects:

The foreign key constraint fax_messages_fax_fkey from the fax_messages table.
The view faxes.

To drop the fax column together with the dependent objects, you can use the CASCADE option:

Finally, drop the fax column with its dependent objects using the CASCADE option:

ALTER TABLE suppliers 
DROP COLUMN fax CASCADE;

Notice:

NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to constraint fax_messages_fax_fkey on table fax_messages
drop cascades to view faxes

The statement removes the fax column from the suppliers table, drops the foreign key constraint fax_messages_fax_fkey, and the view faxes.

Dropping Multiple Columns #

First, drop the phone and email columns from the suppliers table:

ALTER TABLE suppliers
DROP COLUMN phone,
DROP COLUMN email;

Second, show the table structure to verify the changes:

d suppliers;

Output:

                                Table "public.suppliers"
 Column  |          Type          | Collation | Nullable |           Default
---------+------------------------+-----------+----------+------------------------------
 id      | integer                |           | not null | generated always as identity
 name    | character varying(255) |           | not null |
 address | text                   |           |          |
Indexes:
    "suppliers_pkey" PRIMARY KEY, btree (id)

Summary #

Use the PostgreSQL ALTER TABLE DROP COLUMN statement to drop one or more columns from a table.
Use the ALTER TABLE DROP COLUMN statement with the CASCADE option to drop a column and its dependent objects.

Quiz #

Quiz

PostgreSQL DROP COLUMN Statement

5 questions

To help you understand how to drop a column from a table using the PostgreSQL ALTER TABLE DROP COLUMN statement.

Was this helpful?