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: