DELETE RETURNING clause in PostgreSQL

The standard DELETE statement in SQL returns the number of deleted rows.

DELETE FROM external_data;
DELETE 10

In PostgreSQL you can make DELETE statement return something else. You can return all rows that have been deleted.

DELETE FROM external_data RETURNING *;
 id |        creation_date      | user_id |      data 
----+---------------------------+---------+---------------- 
101 | 2014-05-06 13:10:45.09484 |    23   | 'Some text'
102 | 2014-06-10 22:23:12.12045 |    25   | 'Some other text'
(2 rows) 

DELETE 2

You can return the columns of your choice.

DELETE FROM external_data RETURNING id;
 id 
----
101 
102 
(2 rows) 

DELETE 2

In your code you can process the returned rows in the same way as you would process the results of an SQL query. For example, you may log the data that have been deleted.

If you'd like to learn more about the commands INSERT, UPDATE, or DELETE in PostgreSQL, I recommend our interactive course PostgreSQL INSERT, UPDATE, and DELETE Commands. It contains 80 hands-on exercises that cover basic and advanced features of the commands in PostgreSQL.