UPDATE with RETURNING clause in PostgreSQL

Introduction

The UPDATE statement changes data in a table. The RETURNING clause shows the changed rows right away.

You want to update a customer's address and see the new address immediately.

You need to change product prices and get the updated prices back in one step.

You want to mark tasks as done and see which tasks were updated.

You update user roles and want to confirm the changes without a separate query.

Syntax

PostgreSQL

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
RETURNING column1, column2, ...;

The RETURNING clause lists columns you want to see after the update.

If you omit WHERE, all rows will be updated and returned.

Examples

Increase salary by 10% for Sales department and return employee IDs and new salaries.

PostgreSQL

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales'
RETURNING id, salary;

Reduce stock by 1 for product with id 101 and return its id and updated stock.

PostgreSQL

UPDATE products
SET stock = stock - 1
WHERE id = 101
RETURNING id, stock;

Mark all tasks as completed and return their ids and new status.

PostgreSQL

UPDATE tasks
SET status = 'completed'
RETURNING id, status;

Sample Program

This creates a users table, adds three users, then increases Bob's age by 1 and returns his id, name, and new age.

PostgreSQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  age INT
);

INSERT INTO users (name, age) VALUES
('Alice', 30),
('Bob', 25),
('Carol', 27);

UPDATE users
SET age = age + 1
WHERE name = 'Bob'
RETURNING id, name, age;

Important Notes

RETURNING helps avoid running a separate SELECT after UPDATE.

You can return all columns with RETURNING * if you want.

Be careful with UPDATE without WHERE; it updates all rows.

Summary

UPDATE changes data in a table.

RETURNING shows updated rows immediately.

This saves time by combining update and fetch in one step.