This short tutorial will show you how to completely delete/destroy/drop a table in PostgreSQL using the DROP TABLE statement, and provide examples.
Before you Delete a Table…
There are a few things you should check before you try and destroy a table in PostgreSQL, especially if you’re working on a production database that is serving users.
First and most important, take a backup of your PostgreSQL server – just in case you delete the wrong table, or change your mind later.
Next, make sure you’re logged in as the default postgresql admin user, or a user with permission to delete the table in question.
And finally, make sure you’re sure of the name of both the table and the database that it’s in before you delete it, and that there’s nothing you need to keep in it, and that no other databases, table relationships, or systems are relying on it. Deleting cannot be undone (unless you have a backup to restore).
How to Drop a PostgreSQL Table in a Database using DROP TABLE
The DROP TABLE statement is used in PostgreSQL to delete tables. The syntax is as follows:
DROP TABLE table_name;
The table and all data in it will be deleted at the execution of the command.
Destroying Multiple Tables
Multiple tables can be dropped in a single command by listing them:
DROP TABLE table_1, table_2;
Checking if the Table Exists Before Deleting
If you try to delete a table that does not exist, you’ll get an error. This can be avoided by only dropping the table if it exists:
DROP TABLE IF EXISTS table_name;
Cascade Delete
It is possible to delete a table, as well as all tables that reference it using foreign keys.
DROP TABLE table_name CASCADE;
For example, if you had a table called pets that references a table called owners, dropping the owners table with the CASCADE statement would also delete the pets table.
Doing this is quite dangerous, and generally not advised – deleting data should be done carefully and methodically in production so that data intended to be kept is not lost.
Safely Deleting Tables with RESTRICT
If you are deleting a table, but want to make sure that nothing relies on it before it is deleted, the RESTRICT clause will prevent the deletion from occurring if there are any PostgreSQL objects that depend on the table:
DROP TABLE table_name RESTRICT;
Following from the above pets/owners example, an attempt to delete the owners table while the pets table exists and references it with a foreign key would result in the attempt to delete the owners table being denied.
