Returning modified rows with RETURNING in PostgreSQL

Introduction

The RETURNING clause lets you see the rows that were changed by an INSERT, UPDATE, or DELETE right away. This helps you confirm what happened without running another query.

After adding new data, to get the new row's ID or details immediately.

When updating records, to check which rows changed and what their new values are.

When deleting rows, to know exactly which rows were removed.

When you want to avoid extra queries to fetch updated or inserted data.

When you need to log or audit changes made by your query.

Syntax

PostgreSQL

INSERT INTO table_name (column1, column2) VALUES (value1, value2) RETURNING *;

UPDATE table_name SET column1 = value1 WHERE condition RETURNING *;

DELETE FROM table_name WHERE condition RETURNING *;

The RETURNING clause goes at the end of your INSERT, UPDATE, or DELETE statement.

Using RETURNING * returns all columns of the affected rows, but you can specify specific columns instead.

Examples

Insert a new employee and get back their ID and name immediately.

PostgreSQL

INSERT INTO employees (name, role) VALUES ('Alice', 'Engineer') RETURNING id, name;

Update Alice's role and return all her updated information.

PostgreSQL

UPDATE employees SET role = 'Senior Engineer' WHERE name = 'Alice' RETURNING *;

Delete Alice's record and return her ID and name to confirm deletion.

PostgreSQL

DELETE FROM employees WHERE name = 'Alice' RETURNING id, name;

Sample Program

This example creates a temporary employees table, inserts a row, updates it, and then deletes it. Each step returns the affected rows so you can see the changes immediately.

PostgreSQL

CREATE TEMP TABLE employees (id SERIAL PRIMARY KEY, name TEXT, role TEXT);

INSERT INTO employees (name, role) VALUES ('Bob', 'Developer') RETURNING id, name, role;

UPDATE employees SET role = 'Lead Developer' WHERE name = 'Bob' RETURNING *;

DELETE FROM employees WHERE name = 'Bob' RETURNING id, name;

Important Notes

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

Using RETURNING can save time and reduce errors by avoiding extra SELECT queries.

Summary

RETURNING shows you the rows changed by INSERT, UPDATE, or DELETE immediately.

You can return all columns with RETURNING * or select specific columns.

This helps confirm changes and avoid extra queries.