Use Psql to Delete a Table in a PostgreSQL Database

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
sudo su - postgres

Then you can start the interactive terminal for Postgres:

1
psql

Create a database for Postgres

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

1
CREATE DATABASE my_db;

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:

1
2
3
4
5
6
CREATE TABLE player(
    ID INT PRIMARY KEY NOT NULL,
    Name VARCHAR(30),
    Age INT,
    Address VARCHAR(50)
);

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

1
2
CREATE TABLE sport
(sports_name TEXT, country TEXT, score INT);

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:

1
2
3
4
5
6
         List OF relations
 Schema |  Name  | TYPE  |  Owner
--------+--------+-------+----------
 public | player | TABLE | postgres
 public | sport  | TABLE | postgres
(2 ROWS)

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:

1
DROP TABLE tb_name;

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

1
2
DROP TABLE [IF EXISTS] tb_name1,
tb_name2 ..., [CASCADE | RESTRICT ];

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:

1
DROP TABLE player;

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:

1
DROP TABLE player, sport;

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:

1
DROP TABLE IF EXISTS player, sport;

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:

1
psql -c 'DROP TABLE IF EXISTS sport;'

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:

1
psql -U postgres -d template1 -c 'DROP TABLE IF EXISTS sport;'

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.

Screenshot of a psql delete table example to DROP a Postgres table

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.