PostgreSQL: How to Delete a Row and Return Deleted Record

Introduction

When working with PostgreSQL, there may be instances where you not only want to delete a record but also retrieve it for logging or further processing. This tutorial will guide you through the syntax and steps required to delete a row and return the deleted record in PostgreSQL.

Basic Row Deletion

Before delving into more complex examples, let’s start with the basics of deleting a row in PostgreSQL:

DELETE FROM table_name WHERE condition;

In this basic form, the DELETE statement removes the row(s) that match the given condition, but it does not return any information about the deleted rows. To overcome this, PostgreSQL provides the RETURNING clause.

Using the RETURNING Clause

The RETURNING clause is a powerful feature that allows the retrieval of rows affected by a DELETE operation. Here’s how you can use it:

DELETE FROM table_name WHERE condition RETURNING *;

By appending RETURNING * to the end of your DELETE statement, you can return the deleted row. Replace the * with specific column names to return only certain fields.

Identifying the Exact Row to Delete

To ensure you’re deleting the correct row, it’s crucial to precisely identify it using a unique key, usually the primary key:

DELETE FROM table_name WHERE id = specific_id RETURNING *;

In this scenario, id would be your primary key column, and specific_id would be the value you want to match for deletion.

Deleting and Returning Multiple Rows

If you need to delete multiple records and return them in a single operation, the same RETURNING clause will work:

DELETE FROM table_name WHERE condition_to_match_many_rows RETURNING *;

This operation will delete every row that matches the condition and return them as a result set.

Using DELETE with Joins

For more advanced scenarios, you might need to delete rows from one table based on data in another table. PostgreSQL allows you to combine DELETE statements with USING to specify additional tables:

DELETE FROM table1 t1 
USING table2 t2 
WHERE t1.id = t2.ref_id 
RETURNING t1.*;

In this instance, rows from table1 are deleted based on a related row in table2, and the deleted records from table1 are returned.

Working with RETURNING into a Variable or Record

Especially within functions or stored procedures, you may want to store the result of a deleted row into a variable. This can be done using PostgreSQL’s INTO clause:

DO $ 
DECLARE
  deleted_row table_name%ROWTYPE; 
BEGIN 
  DELETE FROM table_name WHERE id = specific_id RETURNING * INTO deleted_row; 
END $;

This will store the deleted record into the variable deleted_row for later use within the block of code.

Integrating with Client Applications

When working with PostgreSQL from a client application, such as those written in Python with the psycopg2 library, you can directly interact with the deleted records:

import psycopg2

conn = psycopg2.connect(connection_parameters)
cursor = conn.cursor()

deleted_id = 123
cursor.execute("DELETE FROM table_name WHERE id = %s RETURNING *", [deleted_id])
deleted_record = cursor.fetchone()

conn.commit()
cursor.close()
conn.close()

After executing the DELETE statement with the RETURNING clause, the fetchone() method retrieves the deleted record for the application’s use.

Concluding the Delete Operation with a Transaction

It’s important to remember that all DELETE operations should be performed within the context of a database transaction. This ensures that you can roll back the deletion if something goes wrong.

BEGIN;

DELETE FROM table_name WHERE id = specific_id RETURNING *;

-- Check results or perform additional operations

COMMIT; -- or ROLLBACK if necessary;

This pattern makes sure your delete operations are safe and consistent.

Conclusion

In this tutorial, we’ve seen various ways to delete rows in PostgreSQL and immediately retrieve information about what was removed using the RETURNING clause. By learning these techniques, you can effectively manage data deletions while also capturing data for your auditing or business logic needs. Always test your delete operations in a controlled environment to ensure accuracy and protect data integrity.