How to Drop All Tables in PostgreSQL Database
Tags: postgres, database, sql
Published: 2020-10-19 20:24:36

---
title: "How to Drop All Tables in PostgreSQL Database"
published: true
description: 
tags: ["postgresql", "postgres", "database", "sql"]
canonical_url: https://kags.me.ke/post/postgresql-how-to-drop-all-tables/
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/j5w859xlmb8iqhev4tz9.png
---

## 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 ]`](https://www.postgresql.org/docs/current/sql-droptable.html)  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](https://www.pgadmin.org/), [Beekeeper Studio](https://www.beekeeperstudio.io/), [Adminer](https://www.adminer.org/) 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.

  {% gist https://gist.github.com/kagundajm/ea1d07bac1db6ce780fabb61116b666b %}

[`DO`](https://www.postgresql.org/docs/13/sql-do.html) 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 [`$$`](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING) 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`](https://www.postgresql.org/docs/13/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS) to hold the table names.

[`current_schema()`](https://www.postgresql.org/docs/current/functions-info.html) 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](https://www.postgresql.org/docs/13/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) 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.