How to Drop a Table in PostgreSQL?

INTRODUCTION

The DROP TABLE statement is used to remove a table from the database:

In this syntax,

• After the DROP TABLE keywords, add the name of the table you want to delete first.
• Second, remove the table only if it is there by using the IF EXISTS option.

PostgreSQL generates an error if a table that doesn't exist is deleted. You can use the IF EXISTS option to prevent this scenario.

The DROP TABLE command cannot remove a table if it is referenced by other objects like views, triggers, functions, and stored procedures. You can choose between two alternatives here:

• The table and any objects depending on it can be deleted using the CASCADE option.
• If any object depends on the table, the RESTRICT option refuses the removal. If you don't specifically express it in the DROP TABLE statement, the RESTRICT option is the default.

You can add a list of tables, separated by commas, following the DROP TABLE keywords to delete several tables at once:

Keep in mind that in order to delete tables, you must be the superuser, schema owner, or table owner.

Examples of PostgreSQL DROP TABLE

Let's look at a few instances of the PostgreSQL DROP TABLE statement in action.

1)Drop any tables that aren't there.

The author table in the database is deleted with the following statement:

Due to the absence of the author table, PostgreSQL generates an error.

You can utilize the IF EXISTS option in this way to get around the problem.

NOTICE: table "author" skipped because it is empty STRIKE TABLE

The output clearly shows that PostgreSQL delivered a notice rather than an error.

2) Eliminate a table with dependent elements.

The authors and pages tables are created by the following:

The author table is dropped using the DROP TABLE command in the following sentence:

PostgreSQL generates an error because the constraint on the page table depends on the author table:

In this situation, either drop the author table first and then all dependent objects, or utilize the CASCADE option as described below:

Both the constraint in the page table and the author table are deleted by PostgreSQL.

The following message will be displayed if the DROP TABLE statement deletes the table's dependent objects:

NOTICE:  drop cascades to constraint pages_author_id_fkey on table pages

Conclusion

• To delete a table, use the DROP TABLE statement.
• To remove a table and all of its dependent items, use the CASCADE option.