DROP TABLE in PostgreSQL

The DROP TABLE statement is used to delete a table and all of its data from the database. Once a table is dropped, the data and structure are permanently removed, so it should be used with caution.

Syntax

DROP TABLE [IF EXISTS] table_name [, table_name2, ...] [CASCADE | RESTRICT];

• table_name: The name of the table to be deleted.
• IF EXISTS: Optional clause to prevent an error if the table does not exist.
• CASCADE: Automatically drops objects that depend on the table (e.g., views, foreign key constraints).
• RESTRICT: Prevents the table from being dropped if there are any dependent objects (default behavior).

1. Basic Example

This will drop a table named employees:

DROP TABLE employees;

This will delete the employees table and all its data.

2. Using IF EXISTS

The IF EXISTS clause ensures that no error occurs if the table doesn’t exist. It simply does nothing if the table is not found.

DROP TABLE IF EXISTS employees;

This will safely attempt to drop the employees table, but if it doesn't exist, no error will be thrown.

3. Dropping Multiple Tables

You can drop multiple tables in a single statement by separating them with commas.

DROP TABLE employees, departments;

This will drop both the employees and departments tables.

4. Using CASCADE

The CASCADE option automatically drops any dependent objects (such as views, foreign key constraints, etc.) that rely on the table being dropped.

DROP TABLE employees CASCADE;

This will drop the employees table and any objects that depend on it.

5. Using RESTRICT

The RESTRICT option prevents dropping a table if there are dependent objects. This is the default behavior, so it's optional to specify.

DROP TABLE employees RESTRICT;

This will attempt to drop the employees table but will fail if there are any dependent objects (e.g., foreign key constraints).

Important Considerations

• Data loss: Dropping a table removes all the data and the structure, and it cannot be undone unless there is a backup.
• Permissions: You must have appropriate privileges to drop a table (e.g., being the table owner or having SUPERUSER privileges).
• Dependencies: Be aware of objects that depend on the table, like foreign keys, triggers, and views.