Drop Tables in PostgreSQL Database

Use the DROP TABLE command to drop the existing table from the database along with data in PostgreSQL Database. Only its owner may destroy a table.

Syntax:

DROP TABLE [IF EXISTS] [<table1>, <table2>...] [CASCADE | RESTRICT];

The following will drop the employee table:

DROP TABLE IF EXISTS employee;

Use IF EXISTS option to drop a table only if it exists in the database. If the specified table does not exist in the database, then it will give a warning and skip the drop command.

If a table is being used in other tables, views, etc., then it will not be dropped from the database because the default mode is RESTRICT. For example, the following department table is being referenced in the employee table as a foreign key.

CREATE TABLE employee( emp_id INT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, gender CHAR(1), dept_id INT NOT NULL, FOREIGN KEY (dept_id) REFERENCES department(dept_id) ); CREATE TABLE department ( dept_id INT PRIMARY KEY, dept_name VARCHAR(100) NOT NULL );

Now, trying to drop the department table will throw an error, as shown below.

Use CASCADE to drop a table even if it is being used in other objects.

DROP TABLE department CASCADE;

If a table is referenced by a view or a foreign key constraint, then use the CASCADE parameter to remove the dependent objects such as views, procedures, but in the case of the foreign key, it will just remove a foreign key constraint from a child table, not the child table itself.

Specify two or more tables separated by a comma to drop multiple tables.

DROP TABLE person, employee;

If you want to delete all the data from a table without dropping a table, then use the DELETE or TRUNCATE TABLE command.