In PostgreSQL, the DELETE query is used to remove/delete a single, multiple, or all the rows of a table. Within the DELETE query, the WHERE clause is used to specify a condition or a group of conditions. In such a case, the DELETE query will delete the table’s record based on the specified condition. Omitting the WHERE clause will delete all the data/records from the targeted table.

This write-up will explain all the basics of the Postgres DELETE query with the help of some examples. So, let’s begin!

How to Use DELETE Query in PostgreSQL?

You have to follow the below-mentioned syntax to use the DELETE query in the PostgreSQL:

DELETE FROM tab_name
WHERE [condition];

Here, in the above syntax:

- tab_name represents a table whose record will be deleted.

- condition represents a criterion based on which the table’s record will be deleted.

- A single or a group of conditions can be used in the WHERE clause using the Boolean Operators i.e. AND and OR.

Now, let’s jump into the practical implementation of the DELETE Query.

How to Delete a Single Row in PostgreSQL using the DELETE Query?

We will understand the working of the DELETE query step-by-step:

Step 1: Retrieve the Table Details Using SELECT Query

We have already created a table “student_details”. Let’s run the SELECT query to see all the details of the “student_details” table:

SELECT * FROM student_details;

The above figure signifies that the “student_details” table has ten rows. Suppose we need to delete the student record whose id is 8.

Step 2: Delete a Record Using DELETE QUERY

Utilize the WHERE clause with the DELETE Query to delete a single record from the selected table. Within the WHERE clause, specify a condition based on which the selected record will be deleted:

DELETE FROM student_details
WHERE student_id = 8;

The output shows that one record has been deleted successfully from the selected table.

Step 3: Verify the Deleted Record Using SELECT QUERY

Run the SELECT query to retrieve the table details:

SELECT * FROM student_details;

The output authenticates that the student having id 8 has been deleted from the student_details table.

How to Delete Several Rows in Postgres using the DELETE Query?

In PostgreSQL, IN operator can be used in the WHERE clause of the DELETE query to delete multiple rows.

Step 1: Delete the Multiple Rows Using DELETE Query

Let’s run the DELETE command with the aid of the WHERE clause and IN operator to delete multiple rows of the table:

DELETE FROM student_details
WHERE student_id IN (2, 4);

The above snippet shows that two rows have been deleted from the studend_details table.

Step 2: Verify the Rows Deletion Using Select Query

Execute the SELECT query to see all the rows of the student_detail table:

SELECT * FROM student_details;

The output clarifies that two students having id 2 and 4 have been deleted from the studend_details table.

How to Delete All Rows in PostgreSQL using the DELETE Query?

Omitting the WHERE clause allows us to delete all rows of the selected table. Follow the below-given steps to delete all the rows of the selected table:

Step 1: Execute the DELETE Query to Delete All Rows of the Table

Run the below-given query to delete all the rows of the student_details table:

DELETE FROM student_details;

The output confirms that all six rows of the student_details table have been deleted successfully.

Step 2: Verify the Rows Deletion Using SELECT Query

SELECT * FROM student_details;

The above snippet verifies that all the rows of the student_details table have been deleted.

RETURNING Clause in Postgres DELETE Query

In PostgreSQL, the DELETE query can accept a RETURNING clause that returns the deleted rows. The syntax of the DELETE query with the RETURNING clause will look like this:

DELETE FROM tab_name
WHERE [condition]
RETURNING *;

The RETURNING clause will come after the WHERE clause. Let’s run the following query to get a profound understanding of the RETURNING clause:

DELETE FROM student_details
WHERE student_id = 6
RETURNING *;

The above snippet verifies that the RETURNING clause returns the deleted row.

From the examples discussed in this write-up, we can conclude that the DELETE query can be used with or without the WHERE clause to delete a single, multiple, or all the rows of a specific table.

Conclusion

In PostgreSQL, the DELETE query is used to delete a single, multiple, or all the rows of a specific table. In the DELETE query, the WHERE clause is used to specify a single or a group of conditions to delete a specific record of a table. Skipping the WHERE clause will delete all the rows of the targeted table. This write-up considered multiple scenarios to explain the working of the Postgres DELETE query in a better way.