PostgreSQL – DROP COLUMN
PostgreSQL also supports the DROP COLUMN clause in the ALTER TABLE statement to remove one or more columns of a table. When you remove a column from a table, PostgreSQL will automatically remove all of its indexes and constraints involving the column.
Syntax: ALTER TABLE table_name DROP COLUMN column_name;
Now let’s look into an example.
Example:
First, we create a few tables (say, books
, categories
, and publishers
) for demonstration having the below depicted relationships:
We create the above tables using the below statements:
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:
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;
If we want to remove the category_id
column of the books
table, we use the following statement:
ALTER TABLE books DROP COLUMN category_id;
Now we check the books table using the below statement:
SELECT * FROM books;
Output: