PostgreSQL provides a wide range of commands, clauses, built-in functions, etc. to facilitate its users. Users can store, retrieve, and manipulate the data effectively using these commands, functions, and queries. A frequently performed database operation is deleting a specific record or a set of records that can be done using the DELETE command. The DELETE command retrieves the number of deleted records on successful execution. In Postgres, the DELETE RETURNING clause can be used to extend the functionalities of the standard DELETE command.

This write-up will discuss what is DELETE RETURNING and how to use it in PostgreSQL.

What Does the DELETE RETURNING Clause Do in PostgreSQL?

In PostgreSQL, the DELETE RETURNING clause not only deletes the selected rows but also retrieves the deleted rows. The example of using the DELETE RETURNING clause is depicted in the following snippet:

DELETE tab_name
RETURNING *;

Here in the above syntax, the “*” indicates that all the deleted rows will be retrieved on successful execution of the “DELETE RETURNING” clause.

Example 1: How to Use DELETE RETURNING Clause in Postgres?

Suppose we have a table named “emp_dat” that contains the following records:

SELECT * FROM emp_dat;

Now the need of the moment is to delete all those employees whose id is greater than or equal to five. Also, the users want to retrieve the deleted records so that they can confirm the deleted records. Both these operations can be done in one go using the “DELETE RETURNING” clause as follows:

DELETE FROM emp_dat
WHERE id >= 5
RETURNING *;

The output shows that the DELETE RETURNING clause not only deleted the selected records but also retrieved them as output.

Example 2: How to Use DELETE RETURNING Clause on Specific Columns in Postgres?

The DELETE RETURNING clause can be used to delete the selected records and retrieve only specific columns:

DELETE FROM emp_dat
WHERE id >= 3
RETURNING name;

The above query will delete all those employees that meet the described criteria. However, instead of returning all columns, the RETURNING clause will retrieve only the employee names from the “name” column:

That was all about the use of the DELETE RETURNING clause in Postgres.

Conclusion

In PostgreSQL, the DELETE RETURNING clause not only deletes the selected rows but also retrieves the deleted rows. The DELETE RETURNING clause saves a lot of time for the user as it deletes and retrieves the selected rows in one go. The DELETE RETURNING clause can also be used to delete the selected records and retrieve only specific columns. This post has explained the usage of the DELETE RETURNING clause in Postgres using various examples.