Use Psql to Delete a Table in a PostgreSQL Database

Introduction

When you’re working with data stored in PostgreSQL tables, there may be times you need to delete a table from your database. Fortunately, the psql command-line interface makes it easy to accomplish this task. In this article, we’ll show you how to access a database and use psql to delete a table in PostgreSQL.

Prerequisites

In order to follow along with this tutorial, a few key prerequisites need to be in place:

• You’ll need to have a PostgreSQL database cluster installed on your machine. If you’re running a Linux distribution that uses systemd, you can use the command systemctl status postgresql to verify that you have Postgres installed.

• You’ll need to know some basic terminal or command prompt commands

• You’ll also need to have access to an existing PostgreSQL user and database or create them if necessary.

Connect to psql

Before you can execute any database commands, you’ll need to open the command-line interface.

To connect to psql, you’ll first need to switch to the postgres user:

Then you can start the interactive terminal for Postgres:

Create a database for Postgres

Once you’re connected to psql, you can create a PostgreSQL database to use in this tutorial:

If you’d like to see a list of all the databases, enter the \l command in psql. You can then choose which database you want to use.

To connect to psql and access a specific database, type \c, followed by the database name, and press return.

Create a table for Postgres

Next, we’ll create a table in PostgreSQL. The basic syntax for creating a table looks like this:

Here’s the SQL statement we’ll use to create our table:

You’ll get a response of CREATE TABLE, which indicates that your table was created successfully.

NOTE: You can use the command \dt to display a list of all tables that you’ve created in a given database.

We’ll create a total of two PostgreSQL tables, which can be seen in the list below:

Use Postgres to delete a table with ‘DROP’

In PostgreSQL, the DROP statement is used to remove or delete a PostgreSQL table.

Shown below is the basic syntax for DROP statement:

Here’s the full syntax, which contains some additional clauses and options:

Let’s take a closer look at this syntax:

• The table name comes after the DROP TABLE clause.

• The IF EXISTS clause can be added after the DROP TABLE statement. This clause will prevent an error from being raised if the table in question doesn’t exist.

• The tb_name1 represents the name of table to be deleted with the DROP statement.

• The tb_name1, tb_name2 represent the names of tables to be deleted if you’d like to remove more than one table.

• The CASCADE and RESTRICT constraints are optional. With CASCADE, objects that depend on the table will be dropped when the table is dropped. The RESTRICT constraint, on the other hand, will drop a PostgreSQL table only when no other objects depend on it.

Now that we understand how the DROP statement works, let’s try some examples using it:

Delete a single table in PostgreSQL

To DROP just one table in PostgreSQL, all you have to do is pass the table name to the DROP TABLE SQL statement:

The table named player was deleted using this statement. You can verify that the table was indeed dropped by using the Postgres \dt command to list all of the database’s tables.

Delete multiple tables in PostgreSQL

The DROP table statement can also be used to delete more than one table. When you drop multiple tables, be sure the table names are separated by commas. You can see how this works in the example below:

This statement would delete the two tables named player and sport.

Let’s imagine that we’re not completely sure both those tables exist in our database. We can modify our DROP statement to use the IF EXISTS clause:

If one of these tables doesn’t exist, PostgreSQL will not raise an error.

Use psql to delete a table

You can also execute SQL commands and statements outside of psql using a terminal or command prompt terminal. This is done by passing the -c flag to the psql command.

In the following example, the psql -c command is run outside the PostgreSQL command-line interface. You can instruct Postgres to DROP a table by passing the SQL statement as a string to the command:

If you get a user does not exist error, or if the table doesn’t exist, then you may need to specify the username and database using the -U and -d flags. Here’s an example:

NOTE: template1 is a built-in, default Postgres database.

If the deletion was successful, Postgres will return a response of DROP TABLE to the terminal or command prompt window.

Conclusion

If you need to drop a table in PostgreSQL, it’s important to know how to do it properly, since the delete operation is permanent and can’t be undone. In this article, we explained how to use psql to delete a table in PostgreSQL. With our instructions and examples, you’ll be able to drop one or more tables from your own PostgreSQL database.