RETURNING clause mental model in PostgreSQL

Introduction

The RETURNING clause lets you get back data from rows you just changed in the database. It saves time by showing results right after an insert, update, or delete.

After adding a new user, get their new ID without a separate query.

When updating a product price, see the new price immediately.

Delete old records and know exactly which ones were removed.

Insert multiple rows and get back all their generated keys at once.

Syntax

PostgreSQL

INSERT INTO table_name (columns) VALUES (values) RETURNING column_list;

UPDATE table_name SET column = value WHERE condition RETURNING column_list;

DELETE FROM table_name WHERE condition RETURNING column_list;

The RETURNING clause comes at the end of INSERT, UPDATE, or DELETE statements.

You can list one or more columns to get back, or use RETURNING * to get all columns.

Examples

Insert a new user and get back the new user's ID.

PostgreSQL

INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]') RETURNING id;

Increase prices of all books by 10% and see their new prices and IDs.

PostgreSQL

UPDATE products SET price = price * 1.1 WHERE category = 'Books' RETURNING id, price;

Remove old sessions and get the IDs of deleted sessions.

PostgreSQL

DELETE FROM sessions WHERE last_active < '2024-01-01' RETURNING session_id;

Sample Program

This example creates a temporary employees table, inserts two employees and returns their details, updates John's salary and returns the updated row, then deletes employees with salary less than 60000 and returns their info.

PostgreSQL

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

INSERT INTO employees (name, salary) VALUES ('John', 50000), ('Jane', 60000) RETURNING id, name, salary;

UPDATE employees SET salary = salary + 5000 WHERE name = 'John' RETURNING id, name, salary;

DELETE FROM employees WHERE salary < 60000 RETURNING id, name;

Important Notes

RETURNING helps avoid extra queries to fetch data after changes.

It works only in PostgreSQL and some other databases, not all SQL systems support it.

Use RETURNING * carefully if your table has many columns, to avoid large results.

Summary

RETURNING clause returns data from rows affected by INSERT, UPDATE, or DELETE.

It saves time by combining data change and retrieval in one step.

Use it to get new IDs, updated values, or deleted row info immediately.