DELETE with RETURNING clause in PostgreSQL

Introduction

The DELETE statement removes rows from a table. The RETURNING clause lets you see which rows were deleted right away.

You want to delete old records but also keep a copy of what was removed.

You need to delete a user but want to log their details immediately.

You want to remove expired sessions and see their IDs for cleanup.

You want to delete orders but return the order numbers for confirmation.

Syntax

PostgreSQL

DELETE FROM table_name
WHERE condition
RETURNING column1, column2, ...;

The RETURNING clause lists columns to show from deleted rows.

If you want all columns, use RETURNING *.

Examples

Deletes the employee with id 5 and returns their name and department.

PostgreSQL

DELETE FROM employees
WHERE id = 5
RETURNING name, department;

Deletes all expired sessions and returns all columns of those deleted rows.

PostgreSQL

DELETE FROM sessions
WHERE expired = true
RETURNING *;

Sample Program

This example creates a temporary users table, inserts three users, then deletes the inactive ones. It returns the id and name of deleted users.

PostgreSQL

CREATE TEMP TABLE users (id SERIAL PRIMARY KEY, name TEXT, active BOOLEAN);

INSERT INTO users (name, active) VALUES
('Alice', true),
('Bob', false),
('Carol', false);

DELETE FROM users
WHERE active = false
RETURNING id, name;

Important Notes

RETURNING is specific to PostgreSQL and some other databases; not all support it.

It helps avoid running a separate SELECT after DELETE to know what was removed.

Summary

DELETE removes rows from a table based on a condition.

RETURNING shows which rows were deleted immediately.

Use RETURNING * to get all columns of deleted rows.