How to Drop All Tables in PostgreSQL Database

Introduction

The DROP TABLE command in PostgreSQL removes a table definition, all data and indexes of a table from a database. DROP TABLE will fail if the table has other objects that depend on it like views and foreign key definitions. The command will also fail and display a table does not exist message if the table being dropped does not exist. PostgreSQL does not have a drop all tables command and you have to define your own way of performing this task.

In this post, I explain how to drop one or multiple tables from a PostgreSQL database. Finally, I end the post with one approach that can be used to drop all tables from a database.

Drop Table Command Syntax

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] is the formal syntax for deleting a table from a PostgreSQL database. The name of the table is the only required parameter for the drop table command. To run the drop table command successfully, the user running the command must either be the table owner, schema owner or a superuser.

When dropping a table or tables, you may optionally prefix the table names with the schema. A schema is a namespace that contains database objects(except roles and tablespaces) but unlike namespaces in normal programming usage or directories or folders in operating systems lingo, namespaces in PostgreSQL cannot be nested. Omitting a schema in object names automatically assumes a schema named public.

You can run the command or commands from a PostgreSQL interactive terminal (psql) or as a query if you are using graphical user interface administration tools such as pgAdmin, Beekeeper Studio, Adminer or any other GUI tool that will allow you to connect to a PostgreSQL database.

Drop Table Command Explained

DROP TABLE public.table_name; will drop the table table_name from the database. public is the schema that owns the table and may be omitted.Using DROP TABLE table_name; without the schema will also work. To delete more than one table, you can include the tables within the command but separate the table names with commas - DROP TABLE table01, public.table2, myschema.table01;.

IF EXISTS prevents PostgreSQL from throwing an error if the table being dropped does not exist. Running DROP TABLE IF EXISTS table_name; from an interactive terminal will return a NOTICE: table “table_name” does not exist, skipping while Beekeeper Studio responds with Query Executed Successfully: No Results".

Adding a CASCADE to the drop table command also drops any objects that depend on the table (such as views or foreign key references). RESTRICT prevents dropping of a table if any objects depend on the table. If CASCADE is not specified, the drop table command defaults to RESTRICT. Therefore, DROP TABLE table_name; is similar to DROP TABLE table_name RESTRICT;

Dropping All Tables

PostgreSQL does not have a specific command to drop all tables from a database. To drop all tables, we will create an anonymous code block and execute the code block. To accomplish the task, we select all table names for a schema from the information_schema.tables and store the names in a RECORD type variable. We then loop through these table names and execute the drop table command for each table name.

DO executes an anonymous code block in a procedural language. The code block is treated as though it were the body of a function with no parameters and returning no rows. The LANGUAGE is optional and may be written either before or after the code block. When language is omitted, it defaults to plpgsql.

The $$ is a dollar-quoted string constant (dollar quoting) and allow use of single quotes (') or backslashes (\) without having to escape them by doubling ('' or \\). Dollar quoting can be used to replace single quotes anywhere in SQL scripts and are used at the beginning and ending of SQL blocks. If you use more than one set in a block, you can put a token (label) between the $$ to make them unique ($SomeTag$Dianne's horse$SomeTag$).

DECLARE r RECORD; declares a variable of type RECORD to hold the table names.

current_schema() returns the name of the schema that is first in the search path. The current_schema() can be replaced by the actual schema name WHERE table_schema='public'. To drop tables for another schema, replace public with the name of the schema. Using the schema name can be useful to reduce the scope of deletion to only the tables owned by the schema.

BEGIN and END statements are used for wrapping multiple lines of SQL code into a statement block.

As the drop table SQL statements are generated dynamically, we quote the table names using quote_ident function.

You run the anonymous code block in the normal manner that you run a drop table command.

Summary

In this post, we looked the DROP TABLE command to drop a single table and multiple tables from a database. By enclosing the command in an anonymous code block, we extended the command to drop all tables from a PostgreSQL database.

That brings us to the end of the post. I hope you the post was helpful in one way or the other.