Introduction to PostgreSQL DROP COLUMN clause

To drop a column of a table, you use theDROP COLUMN clause in the ALTER TABLE statement as follows:

ALTER TABLE table_nameDROP COLUMN column_name;

When you remove a column from a table, PostgreSQL will automatically remove all of theindexes and constraints that involved the dropped column.

If the column that you want to remove is used in other database objects such asviews,triggers, andstored procedures, you cannot drop the column because other objects depend on it.

In this case, you can use theCASCADE option in theDROP COLUMN clause to drop the column and all of its dependent objects:

ALTER TABLE table_nameDROP COLUMN column_name CASCADE;

If you remove a column that does not exist, PostgreSQL will issue an error. To remove a column if it exists only, you can use theIF EXISTS option as follows:

ALTER TABLE table_nameDROP COLUMNIF EXISTS column_name;

In this syntax, if you remove a column that does not exist, PostgreSQL will issue a notice instead of an error.

If you want to drop multiple columns of a table simultaneously, you use multipleDROP COLUMN clauses in theALTER TABLE statement like this:

ALTER TABLE table_nameDROP COLUMN column_name1,DROP COLUMN column_name2,...;

Notice that you need to add a comma (,) after eachDROP COLUMN clause.

If a table has one column, you can use drop it using theALTER TABLE...DROP COLUMN statement. Consequently, the table will have no columns.

It’s worth noting that while PostgreSQL allows a table that has no column, it may be not allowed according to the standard SQL.

PostgreSQL DROP COLUMN examples

Let’s look at some examples to see how theALTER TABLE...DROP COLUMN statement works.

We will create three tables:books,categories, andpublishers for the demonstration.

In this diagram, each book has only one publisher and each publisher can publish many books. Each book is assigned to a category and each category can have many books.

The following statements create the three tables:

CREATE TABLE publishers (    publisher_idSERIAL PRIMARY KEY,    name VARCHAR(255)NOT NULL);CREATE TABLE categories (    category_idSERIAL PRIMARY KEY,    name VARCHAR(255)NOT NULL);CREATE TABLE books (    book_idSERIAL PRIMARY KEY,    titleVARCHAR(255)NOT NULL,    isbnVARCHAR(255)NOT NULL,    published_dateDATE NOT NULL,    description VARCHAR,    category_idINT NOT NULL,    publisher_idINT NOT NULL,    FOREIGN KEY (publisher_id)       REFERENCES publishers (publisher_id),    FOREIGN KEY (category_id)       REFERENCES categories (category_id));

Additionally, wecreate a view based on thebooks andpublishers tables as follows:

CREATE VIEW book_infoAS SELECT    book_id,    title,    isbn,    published_date,    nameFROM    books bINNER JOIN publishers    USING(publisher_id)ORDER BY title;

1) Drop a column example

First, drop thecategory_id column in thebooks table using theALTER TABLE...DROP COLUMN statement:

ALTER TABLE booksDROP COLUMN category_id;

Second, view the structure of thebooks table in psql:

\d books

Output:

Table "public.books"     Column     |          Type          | Collation | Nullable |                Default----------------+------------------------+-----------+----------+---------------------------------------- book_id        | integer                |           | not null | nextval('books_book_id_seq'::regclass) title          | character varying(255)|           | not null | isbn           | character varying(255)|           | not null | published_date | date                   |           | not null | description    | character varying      |           |          | publisher_id   | integer                |           | not null |Indexes:    "books_pkey" PRIMARY KEY, btree (book_id)Foreign-key constraints:    "books_publisher_id_fkey" FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id)

The output indicates that the statement removes both thecategory_id column and theforeign key constraint that involves thecategory_id column.

2) Drop a column that is referenced by a constraint

First, attempt to remove thepublisher_id column from thebooks table:

ALTER TABLE booksDROP COLUMN publisher_id;

PostgreSQL issued the following error:

ERROR:  cannotdrop table books column publisher_id because other objects dependon itDETAIL:  view book_info dependson table books column publisher_idHINT:Use DROP ... CASCADEto drop thedependent objects too.

The output states that thebook_info view is using the columnpublisher_id of thebooks table. You need to use theCASCADE option to remove both the publisher_id column andbook_info view as shown in the following statement:

ALTER TABLE booksDROP COLUMN publisher_id CASCADE;

The statement issued the following notice indicating that the view book_info was also removed:

NOTICE:drop cascadesto view book_infoALTER TABLE

3) Drop multiple columns example

To remove bothisbn anddescription columns simultaneously, you can use multipleDROP COLUMN clauses as follows:

ALTER TABLE books  DROP COLUMN isbn,  DROP COLUMNdescription;

When viewing the books table, you’ll see that those columns were removed:

\d books

Output:

Table "public.books"     Column     |          Type          | Collation | Nullable |                Default----------------+------------------------+-----------+----------+---------------------------------------- book_id        | integer                |           | not null | nextval('books_book_id_seq'::regclass) title          | character varying(255)|           | not null | published_date | date                   |           | not null |Indexes:    "books_pkey" PRIMARY KEY, btree (book_id)

Summary

• Use the PostgreSQLALTER TABLE ... DROP COLUMN statement to drop one or more columns from a table.

Last updated onJanuary 25, 2024

On this page

• Introduction to PostgreSQL DROP COLUMN clause
• PostgreSQL DROP COLUMN examples
• Summary