Use Psql to Delete a Table in a PostgreSQL Database
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.
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 postgresqlto 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
sudo su - postgres
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:
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:
CREATE TABLE player(
ID INT PRIMARY KEY NOT NULL,
Here’s the SQL statement we’ll use to create our table:
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:
List OF relations
Schema | Name | TYPE | Owner
public | player | TABLE | postgres
public | sport | TABLE | postgres
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 TABLE tb_name;
Here’s the full syntax, which contains some additional clauses and options:
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
IF EXISTSclause can be added after the
DROP TABLEstatement. 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
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
DROP just one table in PostgreSQL, all you have to do is pass the table name to the
DROP TABLE SQL statement:
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
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:
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:
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
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:
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
-d flags. Here’s an example:
psql -U postgres -d template1 -c 'DROP TABLE IF EXISTS sport;'
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.
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