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.
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 $$;
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';
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%'
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;
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.
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');
You may also like reading the following articles.
- PostgreSQL DROP COLUMN
- PostgreSQL INSERT INTO table
- PostgreSQL ALTER TABLE
- How to backup PostgreSQL database
- How to import CSV file into PostgreSQL
- Postgresql change column data type
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.