JIYIK CN >

When developing an application, developers must understand the different ways they can use to interact with the database. This knowledge will help improve development efficiency because they will know the best way to solve a specific problem.

Delete all tables in the PostgreSQL database

In this tutorial, we will learn different methods that can be used to delete tables in a specific database without deleting the database itself.

We will first create a database in the PostgreSQL database management system, add a few tables, and delete the tables we created using different methods.

Log in to PostgreSQL using the following command.

>psql -U postgres

Create a database and we will use the following queries to save our database tables.

postgres=# create database customer_db;
CREATE DATABASE

To see the databases we \lcreated using the command, this command will return a collection of all databases.

postgres=# \l

Output:

                                          List of databases
    Name     |  Owner   | Encoding |      Collate       |       Ctype        |   Access privileges
-------------+----------+----------+--------------------+--------------------+-----------------------
 customer_db | postgres | UTF8     | English_Kenya.1252 | English_Kenya.1252 |
 postgres    | postgres | UTF8     | English_Kenya.1252 | English_Kenya.1252 |
 template0   | postgres | UTF8     | English_Kenya.1252 | English_Kenya.1252 | =c/postgres          +
             |          |          |                    |                    | postgres=CTc/postgres
 template1   | postgres | UTF8     | English_Kenya.1252 | English_Kenya.1252 | =c/postgres          +
             |          |          |                    |                    | postgres=CTc/postgres
(4 rows)

Note that the database we created named customer_dbis shown in the first row of the table above, and we can \cconnect to it using the command.

postgres=# \c customer_db;

Output:

You are now connected to database "customer_db" as user "postgres".

We need to have some database tables in our database before we can delete them; we can customer_dbcreate three tables in our database using the following queries.

customer_db=# CREATE TABLE customer(id SERIAL NOT NULL UNIQUE,firstName varchar(50),lastName varchar(50),email varchar(50),PRIMARY KEY(id));

customer_db=# CREATE TABLE product(id SERIAL NOT NULL UNIQUE,productName varchar(50),productDescription varchar(50),productPrice integer,PRIMARY KEY(id));

customer_db=# CREATE TABLE cart(id SERIAL NOT NULL UNIQUE,product varchar(50),productPrice integer,productQuantity integer,totalCost integer, PRIMARY KEY(id));

The above query creates three tables. We can use \dtto verify whether these tables have been created.

customer_db=# \dt

Output:

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | cart     | table | postgres
 public | customer | table | postgres
 public | product  | table | postgres
(3 rows)

Drop all tables in a single schema in PostgreSQL

Note that the command we executed above \dtreturned a list of tables in the database, and that the schema of the table was listed as public.

We will drop all the tables in this schema, re-create the schema, and restore the default permissions for the schema as shown below.

customer_db=# drop SCHEMA public CASCADE;

Output:

NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table customer
drop cascades to table product
drop cascades to table cart
DROP SCHEMA

Deleting the schema will cascade the delete operation to the three tables in our database. Use the following commands to create the schema again and grant all roles to the postgresand publicschemas as shown below.

customer_db=# CREATE SCHEMA public;
customer_db=# GRANT ALL ON SCHEMA public TO postgres;
customer_db=# GRANT ALL ON SCHEMA public TO public;

Drop all tables by specifying multiple tables in PostgreSQL

As shown below, we can use SQL dropstatement to drop tables by listing all the tables we want to drop using comma separated list.

After this query, \dtthe table does not find any relations proving that all our tables in the database have been deleted successfully.

customer_db=# drop table if exists customer, product, cart;

pg_tablesTo drop all tables in PostgreSQL use a SQL query with

Since our tables were dropped in the above example, recreate the three tables again using the provided query to drop all the tables below.

We can write a SQL query to select all the tables we want to delete and perform a cascade operation on them.

customer_db=# SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;'
from pg_tables WHERE schemaname = 'public';

Output:

                 ?column?
------------------------------------------
 DROP TABLE IF EXISTS "customer" CASCADE;
 DROP TABLE IF EXISTS "product" CASCADE;
 DROP TABLE IF EXISTS "cart" CASCADE;
(3 rows)

In PostgreSQL, use table_catalogand table_schemato drop all tables

We can write the SQL query as in the example above and provide the catalog as our database and the public schema for dropping the table.

customer_db=# select 'DROP TABLE "' || table_schema || '"."' || table_name || '" CASCADE;' from information_schema.tables where table_catalog = 'customer_db' and table_schema = 'public';

Output:

               ?column?
-----------------------------------------
 DROP TABLE "public"."customer" CASCADE;
 DROP TABLE "public"."product" CASCADE;
 DROP TABLE "public"."cart" CASCADE;
(3 rows)