DROP statement

Overview

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

Running a DROP statement will also delete all existing records from the table.

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.

The DROP example below is executed in the public schema. You can also drop a table from another specific schema. Click here for more info.

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.

If you attempt to use the table for any operation, you will find that the table no longer exists.

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