PostgreSQL drop all tables (With examples)

In this PostgreSQL tutorial, we are going to learn about dropping all tables from a database. Sometimes you need to drop all tables or tables from a specific schema, we are going to cover different ways of dropping all tables in PostgreSQL.

Let’s begin, remember you can follow along with me, and don’t forget to look at the structure of the database which we are going to use. We are going to cover the following topics.

  • postgresql drop all tables
  • postgresql drop all tables in schema
  • postgresql drop all tables starting with
  • postgres drop all tables and indexes
  • postgres drop all tables except one

Please look at the database that I will use through this tutorial, It is a “dvdrental” database of people of the countries like United States of America (USA) which you can find easily if you search online.

Structure of database
Structure of database

Read PostgreSQL TO_NUMBER() function

Postgresql drop all tables

  • PostgreSQL doesn’t have a particular command or function to drop all tables from a database. To drop all tables, we will make an unknown(anonymous) code block and execute the code block.
  • To achieve the task, we select all table names for a schema from the pg_tables and store the names in the RECORD type variable. after at that point loop through these table names and execute the drop table cmd for each table name.
DO $$ DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
    EXECUTE 'DROP TABLE ' || quote_ident(r.tablename) || ' CASCADE';
  END LOOP;
END $$;
Postgresql drop all tables
Postgresql drop all tables

After running the above query, you will see that the public schema contains notables.

Read: Postgres RegEx

Postgresql drop all tables in schema

To drop all tables from a specific schema or in the schema. first, you need to fetch all the tables then filter the tables using the WHERE conditions for a particular schema that you want to drop.

SELECT
  'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' 
from
  pg_tables WHERE schemaname = 'public';
Postgresql drop all tables in schema
Postgresql drop all tables in schema

Read: PostgreSQL DATE_PART() Function

Postgresql drop all tables starting with

  • Sometimes we want to drop all tables start with a prefix name or starting with certain characters or want to drop all tables that match text values against patterns.
  • For that, We have wildcard LIKE an operator as I told before that there is no specific command to drop all tables from a database.
  • So we need to make an unknown(anonymous) code block and execute the code block and it depends upon the method we choose to drop.
SELECT CONCAT('DROP TABLE ',table_name,';')
FROM information_schema.tables
WHERE table_name LIKE 'a%'
Postgresql drop all tables starting with
Postgresql drop all tables starting with

Read: PostgreSQL DROP TABLE

Postgres drop all tables and indexes

Before dropping all tables and indexes we need to know “why do we need to drop indexes” there are simple reasons.

  • Indexes take additional disk space.
  • indexes slow down INSERT,UPDATE and DELETE, but will speed up UPDATE if the WHERE condition has an indexed field.  INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated. 

Before dropping indexes let’s find out about indexes that exist in a schema or tables.

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public'
ORDER BY
    tablename,
    indexname;
Postgres drop all tables and indexes
Postgres drop all tables and indexes

Do you notice in the output of the above query that contains tablename, indexname, indexedef and pg_indexes let’s define each of them?

The pg_indexes shows information about each index in the PostgreSQL database. The pg_indexes contains five columns:

  • schemaname is used to store the name of the schema that contains tables and indexes.
  • tablename is used to store name of the table to which the index belongs.
  • indexname is used to store name of the index.
  • tablespace is used to store name of the tablespace that contains indexes.
  • indexdef: stores index definition command in the form of CREAT INDEX statement.

Now you know about the indexes of the tables, let’s drop them.

Hey, you already dropped it because when you have followed me from starting or while you are running your ‘drop all tables‘ query, actually you are dropping tables with indexes.

Postgres drop all tables and indexes_2
Postgres drop all tables and indexes_2

All you need to do is to check your indexes using the above query that’s how tables and indexes are dropped.

Read: PostgreSQL INSERT Multiple Rows

Postgres drop all tables except one

  • It is the last topic of this section, what will happen if you want to drop all tables except the “actor” and “city” table from the public schema of the above database.
  • You can do this with the drop table command and you may familiar with this command that’s all.
SELECT 'drop table if exists ' || tablename || ' cascade;' 
FROM pg_tables 
WHERE schemaname = 'public' 
AND tablename NOT IN ('city', 'actor');
Postgres drop all tables except one
Postgres drop all tables except one

You may also like reading the following articles.

So, in this section, we learned How to drop all tables from postgresql, How to drop all tables in a schema, and we also covered the following topics.

  • postgresql drop all tables
  • postgresql drop all tables in schema
  • postgresql drop all tables starting with
  • postgres drop all tables and indexes
  • postgres drop all tables except one