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.