PostgreSQL - DROP TABLE

PostgreSQL - Dropping a Table

The PostgreSQL DROP TABLE statement is used to permanently delete a table and all of its associated data, indexes, rules, triggers, and constraints. This is an irreversible action, so it must be used with extreme caution.

Warning: Once a table is dropped, all the information it contained is lost forever. Always ensure you have a recent backup before dropping important tables in a production environment.

Syntax

The modern syntax for DROP TABLE includes options to handle dependencies and prevent errors.

DROP TABLE [ IF EXISTS ] table_name_1, table_name_2, ... [ CASCADE | RESTRICT ];

• IF EXISTS : (Recommended) Prevents the statement from failing if the table does not exist. The command will simply issue a notice and complete, which is very useful in scripts.
• table_name : The name of the table to be dropped. You can list multiple tables separated by commas.
• RESTRICT : This is the default behavior. It prevents the table from being dropped if any other database objects (like a view or a foreign key) depend on it. This is a crucial safety feature.
• CASCADE : This drops the table along with any objects that depend on it. For example, if a view uses this table, CASCADE will drop both the table and the view. Use with caution.

Examples

Let’s create a couple of tables to demonstrate the different drop behaviors.

-- A simple table with no dependencies
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    log_time TIMESTAMPTZ
);

-- A table that will be referenced by another table
CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- A table with a foreign key dependency on the 'authors' table
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT REFERENCES authors(id)
);

1. Dropping a Single Table

The logs table has no dependencies, so it can be dropped easily.

testdb=# DROP TABLE logs;
DROP TABLE

2. Using IF EXISTS

If we try to drop the logs table again, it will fail. Using IF EXISTS turns the error into a notice.

-- This will cause an error
testdb=# DROP TABLE logs;
ERROR:  table "logs" does not exist

-- This will succeed with a notice
testdb=# DROP TABLE IF EXISTS logs;
NOTICE:  table "logs" does not exist, skipping
DROP TABLE

3. Dropping a Table with Dependencies (RESTRICT vs CASCADE)

Now, let’s try to drop the authors table. The books table has a foreign key that references authors, so the default RESTRICT behavior will prevent the drop.

testdb=# DROP TABLE authors;
ERROR:  cannot drop table authors because other objects depend on it
DETAIL:  constraint books_author_id_fkey on table books depends on table authors
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

PostgreSQL gives a helpful error message, suggesting the use of CASCADE. Using CASCADE will drop the authors table AND automatically remove the foreign key constraint from the books table.

testdb=# DROP TABLE authors CASCADE;
NOTICE:  drop cascades to constraint books_author_id_fkey on table books
DROP TABLE

After this command, the authors table is gone, and the books table no longer has a foreign key constraint.

4. Dropping Multiple Tables

You can drop any remaining tables in a single command.

testdb=# DROP TABLE IF EXISTS books, non_existent_table;
NOTICE:  table "non_existent_table" does not exist, skipping
DROP TABLE