In PostgreSQL, the DELETE FROM keyword is used to delete one, multiple, or every single row of a table. To delete multiple rows, the IN operator with the collaboration of the WHERE clause is used in the DELETE statement. PostgreSQL provides an optional parameter named RETURNING that can be used with the DELETE statement to return the currently deleted rows.

This post aims to explain how to delete multiple rows in Postgres with the help of examples. So, let’s get started.

How to Delete Multiple Rows in PostgreSQL?

Use the below syntax to delete multiple rows of a Postgres table:

DELETE FROM tab_name
WHERE col_name IN (row_1, row_2, ..., row_N);

- Firstly, use the DELETE query followed by the FROM keyword, and then specify the table name from which you want to delete the rows.

- Next, specify a column name in the WHERE clause.

- Next, specify the IN operator followed by two parentheses. Within parentheses, specify the list of rows that you want to delete.

Example: How to Delete Multiple Rows Using DELETE Query?

We already have a table in our database named article_details whose details are as follows:

SELECT * FROM article_details;

Executing the below-given query will delete multiple rows from the selected table:

DELETE FROM article_details
WHERE article_id IN (2, 4, 7);

The output shows that three rows have been deleted from the article_details table. Let’s verify rows deletion using the SELECT statement:

SELECT * FROM article_details;

From the output, you can observe that only three rows are left in the articles_details table. The rows having article ids 2, 4, and 7 have been deleted from the selected table.

How to Use RETURNING Clause With DELETE Query?

With the DELETE statement, an optional clause named RETURNING can be used that returns the currently deleted rows. Here is the syntax of the DELETE statement with the RETURNING clause:

DELETE FROM tab_name
WHERE col_name IN (row_1, row_2,   ..., row_N)
RETURNING *;

Example: How to Use the RETURNING Clause With the DELETE Statement in PostgreSQL?

In this example, we will delete and return two rows from the article_details table:

DELETE FROM   article_details
WHERE article_id IN (3, 5)
RETURNING *;

The RETURNING clause returned the recently deleted rows of the selected table. Let’s execute the below command to see the updated table:

SELECT * FROM article_details;

The output shows that the articles having ids 3 and 5 didn’t exist in the table.

Conclusion

In PostgreSQL, the DELETE statement is used with the collaboration of the WHERE clause and IN operator to delete multiple rows from a table. Firstly, use the DELETE query followed by the FROM keyword. Afterward, specify the targeted table’s name. Next, specify a column name in the WHERE clause, and finally, specify the IN operator followed by two parentheses. Within parentheses, specify the list of rows that you want to delete. This write-up shows how to delete multiple rows from a Postgres table with the help of several examples.