​

Overview

In this section, we will learn how to delete the data from a table using the DROP statement.

​

Syntax

The basic syntax for the DROP statement is as follows:

DROP TABLE [IF EXISTS] table_name;

In this syntax:

• table_name defines which table you want to remove.
• IF EXISTS is an optional parameter used to ensure no error occurs if the table does not exist.

​

Examples

​

Case #1: Dropping the Table

1. Use the following query to create the table.

CREATE TABLE warehouse (
  id int,
  product string,
  qty int
);
INSERT INTO warehouse 
    (id, product, qty) 
VALUES 
    (889771,'Shirt',22),
    (777821,'Hat',99),
    (103829,'Bed Cover',12);

2. We can then use the SELECT statement to view the data in the table:

 SELECT * FROM warehouse;

It will generate the following result:

+---------+------------+---------+
| id      | product    | qty     | 
+---------+------------+---------+
| 889771  | Shirt      | 22      |
| 777821  | Hat        | 99      |
| 103829  | Bed Cover  | 12      |
+---------+------------+---------+

3. To delete the warehouse table and all its data, we can use the following query:

DROP TABLE warehouse;

4. If the query is executed successfully, we will get the following output:

DROP TABLE

Query returned successfully in 284 msec.

​

Case #2: Dropping the Table using IF EXISTS

IF EXISTS can be used to prevent errors when dropping the table if the table does not exist.

​

Example without IF EXISTS

1. First, drop the table without using the IF EXISTS option.

DROP TABLE warehouse;

Output:

DROP

2. If you attempt to drop the table again without using IF EXISTS, it will result in an error.

DROP TABLE warehouse;

Output:

ERROR:  relation "warehouse" does not exist

​

Example with IF EXISTS

Now, drop the table using the IF EXISTS.

DROP TABLE IF EXISTS warehouse;

The drop operation proceeds without errors even if the table doesn’t exist.

DROP