Summary: this tutorial shows you how to use the PostgreSQL DROP COLUMN clause in the ALTER TABLE statement to remove one or more columns of a table.
Introduction to PostgreSQL DROP COLUMN clause
To drop a column of a table, you use the DROP COLUMN clause in the ALTER TABLE statement as follows:
1 2 | ALTER TABLE table_name DROP COLUMN column_name; |
When you remove a column from a table, PostgreSQL will automatically remove all of its indexes and constraints involving the column.
If the column that you want to remove is used in other database objects such as views, triggers, stored procedures, etc., you cannot drop the column because other objects depend on it. In this case, you add the CASCADE option to the DROP COLUMN clause to drop the column and all of its associated objects:
1 2 | ALTER TABLE table_name DROP COLUMN column_name CASCADE; |
If you remove a non-existing column, PostgreSQL will issue an error. To avoid this, you can add the IF EXISTS option as follows:
1 2 | ALTER TABLE table_name DROP COLUMN IF EXISTS column_name; |
In this form, 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 in a single command, you use the following statement:
1 2 3 4 | ALTER TABLE table_name DROP COLUMN column_name_1, DROP COLUMN column_name_2, ...; |
Notice that each DROP COLUMN clause is separated by a comma (,).
PostgreSQL allows you to drop the only column of a table, causing a zero-column table, which is not permitted in SQL standard.
Let’s take a look at some examples to see how the ALTER TABLE DROP COLUMN statement works.
PostgreSQL DROP COLUMN examples
We will create three tables: books, categories, and publishers 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | CREATE TABLE publishers ( publisher_id serial PRIMARY KEY, name VARCHAR NOT NULL ); CREATE TABLE categories ( category_id serial PRIMARY KEY, name VARCHAR NOT NULL ); CREATE TABLE books ( book_id serial PRIMARY KEY, title VARCHAR NOT NULL, isbn VARCHAR NOT NULL, published_date DATE NOT NULL, description VARCHAR, category_id INT NOT NULL, publisher_id INT NOT NULL, FOREIGN KEY (publisher_id) REFERENCES publishers (publisher_id), FOREIGN KEY (category_id) REFERENCES categories (category_id) ); |
In addition, we create a view based on the books and publishers tables as follows:
1 2 3 4 5 6 7 8 9 10 11 | CREATE VIEW book_info AS SELECT book_id, title, isbn, published_date, name FROM books b INNER JOIN publishers P ON P .publisher_id = b.publisher_id ORDER BY title; |
Suppose you want to remove the category_id column of the books table, you use the following statement:
1 | ALTER TABLE books DROP COLUMN category_id; |
Let’s show the books table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | test=# \d books; Table "public.books" Column | Type | Modifiers ----------------+-------------------+--------------------------------------------------------- book_id | integer | not null default nextval('books_book_id_seq'::regclass) title | character varying | not null isbn | character varying | 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) |
As you can see, the statement removed not only the category_id column but also the foreign key constraint involving the category_id column.
Let’s try to remove the publisher_id column:
1 | ALTER TABLE books DROP COLUMN publisher_id; |
PostgreSQL issued the following error:
1 2 3 | ERROR: cannot drop table books column publisher_id because other objects depend on it DETAIL: view book_info depends on table books column publisher_id HINT: Use DROP ... CASCADE to drop the dependent objects too. |
It stated that the book_info view is using the column publisher_id of the books table. You need to use the CASCADE option to remove both the publisher_id column and book_info view as shown in the following statement:
1 | ALTER TABLE books DROP COLUMN publisher_id CASCADE; |
The statement issued the following notice, which is what we expected.
1 | NOTICE: drop cascades to view book_info |
To remove both isbn and description columns in a single statement, you add multiple DROP COLUMN clauses as follows:
1 2 3 | ALTER TABLE books DROP COLUMN isbn, DROP COLUMN description; |
It worked as expected.
In this tutorial, you have learned how to use PostgreSQL DROP COLUMN clause in the ALTER TABLE statement to remove one or more columns of a table.