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.
First, we create a few tables (say,
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
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;
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.