DbSchema | PostgreSQL - How to Drop a Table?

PostgreSQL • 12-May-2023

by Malik Umair Sajjad

Table of Contents

Introduction to PostgreSQL
How to Delete a Table in PSQL
How to Delete a Table in DbSchema
Precautions When Deleting Tables
Conclusion
References

Introduction

PostgreSQL, commonly known as “Postgres,” is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It is equipped with a powerful set of features such as full ACID compliance, robustness, and performance, which enable you to manage your data no matter how big or small the dataset. In addition, it has a strong reputation for data integrity and correctness.In this article, we will explore two methods for deleting tables from a database: using the psql command-line interface and using DbSchema, a visual database design tool.

How to Delete a Table in PSQL

Step 1: Firstly, we will connect to our database.To do so you can get read our article on the topic “PostgreSQL-How to Create Database?”.
Step 2: After successfully establishing the connection with the database , we will check all the available databases. To do so we will use following command:

postgres=# \l
List of Databases
Name      | Owner
==================
postgres  | postgres
template0 | postgres 
test      | postgres

Step 3: Next, we will access/select the database where the table to be deleted is located. To do so, type “\c” followed by database name.Suppose our database name is example.

postgres=# \c example
Now you are connected to database example as user postgres

Here,”\c” represents a command while “example” is the database to be accessed.

Step 4: Once you get connected to the database and select the database where the table to be deleted is to be located. The next step is to check all the available tables within the targeted database. To do so we run “\d” command.

\d
List of Relations
Schema      | Name
==================
public  | countries
public  | citites

Step 5: Once we get the list of all the available tables in the targeted databases, we can now drop the tables which we want to delete.

In PostgreSQL, the __DROP TABLE` statement is used to delete an existing table.

Here’s a simple example:

DROP TABLE IF EXISTS company_details , team_details ;

In this example, the __DROP TABLE command is used to delete the tables named "company_details". The __IF EXISTS clause is optional and prevents an error from occurring if the “Employees” table does not exist. It’s a good practice to use this clause to avoid potential issues in your SQL script.

Warning:

Remember, __DROP TABLE` completely removes the table structure and associated data, constraints, triggers, etc. So be very sure that you indeed want to completely remove the table before executing the command.

Precautions When Deleting Tables

Deleting a table is a destructive operation and cannot be undone. Here are some precautions to consider:

Backup your data: Always make sure to have a backup of your data before deleting a table.
Check dependencies: Before deleting a table, check if other objects in the database (like views, stored procedures, triggers) depend on it.
Use transactions: Use the BEGIN, COMMIT, and ROLLBACK commands to control transactions, so if something goes wrong, you can rollback the operation.

Drop Tables and Visually Manage PostgreSQL using DbSchema

DbSchema is a PostgreSQL client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Drop Table >

Drop Table

Start the application and connect to the Postgres database. Right-click any table header from the layout and choose Drop table.

Conclusion

Deleting tables in PostgreSQL, whether using PSQL or DbSchema, is a straightforward process. However it is crucial to exercise caution when deleting tables, as it is an irreversible action that permanently removes the table and its associated data. Always double-check dependencies and ensure you have a backup of your data before proceeding.

PostgreSQL provides the __DROP TABLE` statement in PSQL, allowing you to delete tables with ease. DbSchema, on the other hand, offers a visual interface that simplifies the process of deleting tables within the Schema Designer.

By following the steps outlined in this article, you can confidently delete tables in either PSQL or DbSchema, depending on your preference and requirements.