Home » Programming » Databases » How to Drop/Delete/Destroy a Table in PostgreSQL

How to Drop/Delete/Destroy a Table in PostgreSQL

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:


Cascade Delete

It is possible to delete a table, as well as all tables that reference it using foreign keys.


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:


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.

Photo of author
I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Comment