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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Dropping One Column Example #

First, create a table suppliers 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
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, drop the notes column from the suppliers table.

ALTER TABLE suppliers 
DROP COLUMN notes;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, verify the column removal:

\d suppliersCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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)Code language: plaintext (plaintext)

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
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, drop the fax column from the suppliers table:

ALTER TABLE suppliers 
DROP COLUMN fax;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.Code language: plaintext (plaintext)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 faxesCode language: plaintext (plaintext)

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, show the table structure to verify the changes:

\d suppliers;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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)Code language: plaintext (plaintext)

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 #

Was this tutorial helpful ?