PostgreSQL DROP TABLE Statement

Summary: in this tutorial, you’ll learn how to delete a table from the database using the PostgreSQL DROP TABLE statement.

Introduction to PostgreSQL DROP TABLE statement

Dropping a table from a database means permanently deleting the table and all its data from the database.

Sometimes, you need to drop a table from the PostgreSQL database. For example, when you’re not using the table anymore. Dropping obsolete tables can free up database storage.

The PostgreSQL DROP TABLE statement, a simple and powerful tool, deletes a table from the database.

Here’s the syntax of the DROP TABLE statement:

DROP TABLE [IF EXISTS] table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the name of the table you want to delete after the DROP TABLE keywords.
Second, the IF EXISTS option provides you with the control to remove the table only if the table exists, ensuring your actions are secure.

If you delete a table that does not exist, PostgreSQL will issue an error. However, with the IF EXISTS option, PostgreSQL will not issue any error, providing you with a safety net.

The DROP TABLE statement can drop multiple tables once:

DROP TABLE [IF EXISTS] table_name1, table_name2, ...;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, you specify a comma-separated list of tables after the DROP TABLE keywords

PostgreSQL DROP TABLE statement example

First, you’re working on the inventory management system that categorizes products. You decide to create a new table called categories to store these product categories:

CREATE TABLE categories (
   category_id INT,
   name VARCHAR(255)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, use the DROP TABLE statement to drop the categories table:

DROP TABLE IF EXISTS categories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you check the database, you’ll see that the categories table was deleted.

Summary

Use the DROP TABLE to delete a table from a PostgreSQL database.
Use the IF EXISTS option to avoid the error of deleting a non-existing table.