SQL DELETE statement for PostgreSQL

Introduction

When you’re working with data in PostgreSQL, there will be situations where you need to delete data from your tables. It’s important to know how to construct your SQL statements properly so that you can control what data is deleted. Using the WHERE clause with a SQL DELETE statement can provide the control you need. In this article, we’ll show you how to use the SQL DELETE statement in PostgreSQL to remove data from a specified table.

Prerequisites

Before we dive into our SQL examples, we need to review a few prerequisites that are essential for this tutorial:

  • First, you’ll need to make sure that PostgreSQL is installed. If you’re not sure whether the service is installed, use the service postgresql status command to check if the status is active. When you’re done, just press CTRL + C to exit.

  • You’ll also need to make sure psql is installed. Simply type psql -V to verify that this interactive shell for PostgreSQL is installed.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

Accessing PostgreSQL Using the ‘psql’ Command-line Interface

The first thing we’ll need to do in this tutorial is access a PostgreSQL database. We can use the following command in a terminal window to access a PostgreSQL database using the psql command-line interface:

psql someUserName -h 127.0.0.1 -d some_database

You’ll be prompted for the password associated with the user. After you enter the password and press Return, you’ll find that you have access to the database.

Create a PostgreSQL Database

Next, we’ll create a database in PostgreSQL:

CREATE DATABASE db_name;

We can use the \l SQL command to view a list of all the databases in psql.

NOTE: If you didn’t specify a database upon entering the psql interface, you can use \c db_name to connect to the database.

Create a PostgreSQL Table

Now that we have a new database, let’s create a table in it. The syntax for creating a table in psql SQL command is shown below:

CREATE TABLE TABLE_NAME (column_name + DATA TYPE + constraints [optional] );

For our example, we’ll create a table using the following SQL statement:

CREATE TABLE student(
id INT NOT NULL PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT NOT NULL,
Address TEXT,
Course TEXT
);

This statement will return the following output:

TABLE "public.student"
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
-----------+-----------------------+-----------+----------+---------
id | INTEGER | | NOT NULL |
firstname | CHARACTER VARYING(50) | | |
lastname | CHARACTER VARYING(50) | | |
age | INTEGER | | NOT NULL |
address | text | | |
course | text | | |
Indexes:
"student_pkey" PRIMARY KEY, btree (id)

Inserting Records into a PostgreSQL Table

In order to practice using the SQL DELETE statement in PostgreSQL, we’ll first need to insert some records into our table. The basic syntax for an INSERT statement in PostgreSQL looks like the following:

INSERT INTO TABLE_NAME(column_1, column_2, column_3)
VALUES('values_1', 'values_2', 'values_3');

An example of a real INSERT statement would look like this:

INSERT INTO student(id, FirstName, LastName, Age, Address, Course)
VALUES(1, 'Richard', 'Wrights', 21, 'Texas', 'Computer Science');

For the purposes of our tutorial, we’ve inserted the following records into the student table:

sampledb=# SELECT * FROM student;
id | firstname | lastname | age | address | course
----+-----------+----------+-----+------------+------------------------
1 | Richard | Wrights | 21 | Texas | Computer Science
2 | Harman | Kardon | 22 | California | Architect
3 | Riley | Miles | 23 | Canada | Nursing
4 | Brandon | Willis | 21 | Florida | Engineer
5 | William | Carlsen | 20 | Texas | Teacher
6 | Gracia | Mirables | 25 | Canada | Computer Science
7 | Paul | Davis | 24 | Norway | Accounting AND Finance
8 | Teddy | Chrysler | 23 | Houston | Chemical Engineering
9 | Harley | Kimstar | 25 | California | Marketing
10 | David | Lopez | 22 | Texas | History
(10 ROWS)

Using the SQL ‘DELETE’ Statement in a PostgreSQL Query

The PostgreSQL DELETE statement deletes rows from a specified table using the criteria defined in the WHERE clause.

To use the DELETE statement with a WHERE clause, keep the following points in mind:

  • First, you’ll need to select the table from which you want to DELETE
  • Second, you’ll need to identify which rows should be deleted using a WHERE clause. Using the WHERE clause is optional; however, if you disregard it, the DELETE statement will delete all the records in the table.
DELETE FROM TABLE_NAME
WHERE [CONDITION];

Here’s an example of a simple SQL DELETE statement in PostgreSQL:

DELETE FROM student
WHERE id = 3;

The statement shown above will return results that look like this:

id | firstname | lastname | age | address | course
----+-----------+----------+-----+------------+------------------------
1 | Richard | Wrights | 21 | Texas | Computer Science
2 | Harman | Kardon | 22 | California | Architect
4 | Brandon | Willis | 21 | Florida | Engineer
5 | William | Carlsen | 20 | Texas | Teacher
6 | Gracia | Mirables | 25 | Canada | Computer Science
7 | Paul | Davis | 24 | Norway | Accounting AND Finance
8 | Teddy | Chrysler | 23 | Houston | Chemical Engineering
9 | Harley | Kimstar | 25 | California | Marketing
10 | David | Lopez | 22 | Texas | History

We can see that the row where ID = 3 is no longer in the table.

Conclusion

When you need to remove certain rows from a PostgreSQL table, it’s important to construct your DELETE statement carefully– once data is deleted, it’s gone for good. In this article, we showed you how to use the SQL DELETE statement in PostgreSQL, adding a WHERE clause to the statement to control which rows are deleted. With the SQL examples provided in this tutorial, you’ll be prepared to construct your own DELETE statements to use in PostgreSQL.

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.