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 commandsystemctl 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 theDROP 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.
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