How to drop a table in PostgreSQL

There are a couple of ways to drop/remove a table in PostgreSQL, such as using psql and pgAdmin. In PostgreSQL, the DROP TABLE command is used to delete/drop the targeted table permanently. Therefore, once a table is dropped using the DROP TABLEcommand, it cannot be retrieved.

How to Drop/Delete a table using SQL SHELL (psql) in PostgreSQL?

The DROP TABLE command is used to delete/drop a table and everything that resides within that table permanently. The basic syntax of the DROP TABLE command will be as follows:

DROP TABLE tblname;

Here, the DROP TABLE is a command that drops a table while tblname is the table to be dropped.

How to Drop/Delete Multiple tables in PostgreSQL using psql?

You can drop multiple tables using the DROP TABLE command simultaneously. To do so, you have to follow the comma-separated syntax as shown in the below-given snippet:

DROP TABLE table1, table2, ...;

Here, table1, table2, etc., are the tables to be dropped/deleted.

Let’s follow the below-given steps to understand how to drop a table in PostgreSQL:

Step 1: Check available databases

Type “\l” to check the list of available databases:

\l

image

Step 2: Access/Select the database

Firstly, select/access the database where the table to be deleted is located. To do so, type “\c” followed by database name:

\c example

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

image

Step 3: Check available tables

To get all available tables within the targeted database, run the "/d" command:

\d

image

The above snippet shows that there are two tables (company_details and team_details) within the selected database.

Step 4: Drop the tables

Let’s drop both these tables using the DROP TABLE command:

DROP TABLE company_details, team_details;

image

Step 5: Verify the Table deletion

Let’s execute the “\d” command to verify whether the selected tables have been dropped or not:

\d

image

This is how you can drop a table using the DROP TABLE command from the psql.

How to Drop/Delete a table using pgAdmin in PostgreSQL?

Follow the below-given step-wise instructions to drop a table using the pgAdmin:

Step 1: Select the database

Firstly, open the pgAdmin and select the database in which the targeted table is located:

image

Step 2: Open the Schemas

Let’s click on the schemas and select the public section/option:

image

Step 3: Find the tables section

Scroll down to locate the Tables section. To check the list of available tables, click on the tables section:

image

The above-given snippet shows that there are two tables available in the selected database.

Step 4: Drop the selected table

Select the table to be dropped under the “Tables” section. Right-click on the targeted table and select the Delete/Drop option to drop the unwanted table:

image

Step 5: Confirm the table deletion

Clicking on the Drop/Delete option will show a “drop table” window. Clicking on the “Yes” button will drop/delete the targeted table permanently from the database:

image

Step 6: Verify the table deletion

You can verify the table deletion under the “Tables” section:

image

The above-given snippet shows that the “company_details” table is no longer available under the “Tables” section.

This is how you can drop a table in PostgreSQL by using the SQL SHELL (psql) and pgAdmin.

Conclusion

In PostgreSQL, we can drop a table using SQL SHELL and pgAdmin. Utilize the DROP TABLE command followed by the table name to drop the targeted table from SQL SHELL. While in the case of pgAdmin, select the database > open the public section under schemas > find the targeted table > right-click on the selected table > select the Drop/Delete option to delete the selected table from the database. This write-up explained a couple of table deletion methods with the help of appropriate screenshots.