RETURNING: See the Changed Rows

When you insert, update or delete rows, psql normally just tells you how many rows changed, like UPDATE 1. PostgreSQL can also show you the rows themselves. Add RETURNING at the end of the statement.


Syntax

INSERT INTO ... VALUES (...) RETURNING column1, column2, ...;
UPDATE ... SET ... WHERE ... RETURNING column1, column2, ...;
DELETE FROM ... WHERE ... RETURNING column1, column2, ...;

You can use RETURNING * to return all columns.


Example 1: INSERT with RETURNING

INSERT INTO employees
VALUES (21, 'Emily', 29, 'IT', 'female')
RETURNING id, name;

Output:

 id | name  
----+-------
 21 | Emily
(1 row)

INSERT 0 1

The new row is shown first, then the usual INSERT 0 1.


Example 2: UPDATE with RETURNING

UPDATE employees
SET age = age + 1
WHERE department = 'HR'
RETURNING name, age;

Output:

 name  | age 
-------+-----
 Alice |  29
 Anna  |  25
(2 rows)

UPDATE 2

You can see the new ages right away, without running a separate SELECT.


Example 3: DELETE with RETURNING

DELETE FROM employees
WHERE age < 25
RETURNING *;

Output:

 id | name | age | department | gender 
----+------+-----+------------+--------
  9 | Mike |  24 | IT         | male
(1 row)

DELETE 1

This shows exactly which rows were deleted. Only Mike is gone. Anna was 24, but Example 2 made her 25, so she was not deleted.


Tip

RETURNING is a PostgreSQL feature. Many other databases, like MySQL, do not have it.

In the exercise, update some rows and return the new values.