Open In App
Related Articles

PostgreSQL – DROP COLUMN

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report
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:

Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads