Skip to content
aviral gupta
Get in touch

// B4.2 · ~30 min · Beginner

RETURNING: the rows you just changed

After this lesson you can get a new row’s id back from its INSERT, see what an UPDATE or DELETE changed, and show old and new values side by side.

Lesson 2 of 5 in B4 Changing data and schema

You will be able to

  • Get the rows that INSERT, UPDATE and DELETE changed back with RETURNING, including a generated id
  • Predict what RETURNING shows by default: the inserted row, the new values, the deleted row
  • Show old and new values side by side with PostgreSQL 18’s old. and new., and rename them with WITH

  1. Warm-up · Activity 1 of 7

    Warm-up: the database numbers each new loan itself. How do you learn the number of the loan you just added, in the same statement?

  2. Predict · Activity 2 of 7

    Predict before you read on. Dune has 2 copies, The Hobbit 3, every other book 1. Which title and copies does the UPDATE return?

    UPDATE books
SET copies = copies - 1
WHERE copies > 1
RETURNING title, copies

  3. Practice · Activity 3 of 7

    The loans have the ids 1 to 5. Fill in the column so that the INSERT returns the number PostgreSQL gives the new loan.

    INSERT INTO loans (book_id, member_id, loaned_on) VALUES (4, 4, '2026-10-03') RETURNING ____;
    INSERT INTO loans (book_id, member_id, loaned_on) VALUES (4, 4, '2026-10-03') RETURNING ;

  4. Practice · Activity 4 of 7

    Match each RETURNING to what it shows.

  5. Practice · Activity 5 of 7

    Put the parts of the statement in order.

    1. 1.SET copies = copies + 1
    2. 2.WHERE genre = 'sci-fi'
    3. 3.RETURNING title, old.copies, new.copies;
    4. 4.UPDATE books

  6. Brain teaser · Activity 6 of 7

    Brain teaser. Three books are sci-fi. SET leaves every value as it was. How many rows does this return?

    UPDATE books
SET copies = copies
WHERE genre = 'sci-fi'
RETURNING title

  7. Apply · Activity 7 of 7

    Mini-task. With the library of the worked example: Hedy (hedy@example.com, Vienna) joins on 2026-10-03. Add her with an INSERT that returns her new id. Use that id to record her loan of The Hobbit (book 5) on the same day, again returning the new loan’s id. Then the library buys one more copy of every fantasy book: return each title with its copies before and after.

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

Getting changed rows back

setup.sql loads the library of module B1; here PostgreSQL numbers the members and the loans itself. main.sql adds a loan and gets its id back, marks a loan as returned, adds a copy of every sci-fi book with the values before and after, and deletes a member while showing the deleted row. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. A new loan: PostgreSQL picks the id, RETURNING shows it.
INSERT INTO loans (book_id, member_id, loaned_on)
VALUES (4, 4, '2026-10-03')
RETURNING id, book_id, loaned_on;

-- 2. Kindred comes back: RETURNING shows the row after the update.
UPDATE loans
SET returned_on = '2026-10-03'
WHERE id = 2
RETURNING id, loaned_on, returned_on;

-- 3. PostgreSQL 18: the old and the new value side by side.
UPDATE books
SET copies = copies + 1
WHERE genre = 'sci-fi'
RETURNING title, old.copies AS before, new.copies AS after;

-- 4. A deleted row, shown one last time.
DELETE FROM members
WHERE name = 'Margaret'
RETURNING *;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

-- PostgreSQL numbers the members and the loans itself (lesson B4.4).
CREATE TABLE members (
  id integer GENERATED ALWAYS AS IDENTITY,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members (name, email, city, joined) VALUES
  ('Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  ('Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  ('Linus', NULL, 'Berlin', '2026-02-10'),
  ('Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer GENERATED ALWAYS AS IDENTITY,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans (book_id, member_id, loaned_on, returned_on) VALUES
  (1, 1, '2026-09-01', '2026-09-10'),
  (3, 1, '2026-09-12', NULL),
  (2, 2, '2026-09-05', '2026-09-20'),
  (1, 3, '2026-09-15', NULL),
  (6, 2, '2026-09-18', NULL);

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Output

 id | book_id | loaned_on
----+---------+------------
  6 |       4 | 2026-10-03
(1 row)

 id | loaned_on  | returned_on
----+------------+-------------
  2 | 2026-09-12 | 2026-10-03
(1 row)

    title    | before | after
-------------+--------+-------
 Dune        |      2 |     3
 Kindred     |      1 |     2
 Neuromancer |      1 |     2
(3 rows)

 id |   name   |        email         | city |   joined
----+----------+----------------------+------+------------
  4 | Margaret | margaret@example.com |      | 2026-05-20
(1 row)
  • The INSERT gave no id; PostgreSQL chose 6, the next number, and RETURNING shows it.
  • returned_on is already the new date: an UPDATE’s RETURNING shows the row after the change.
  • old.copies and new.copies show each sci-fi book before and after the update, in one statement.
  • The DELETE returns Margaret’s row as it was; her empty city is NULL.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 2

Get the new ids back

Hedy (hedy@example.com, Vienna) joins the library on 2026-10-03. The starter adds her but returns nothing. Make the INSERT return her id and name. Then add her loan of The Hobbit (book 5) on 2026-10-03, with the id the first statement returned as member_id, and return the loan’s id, book_id and member_id.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    RETURNING goes at the very end of the INSERT, after the VALUES list.

  2. Hint 2

    The first statement returns 5; use it as member_id in the loan.

  3. Hint 3

    INSERT INTO loans (book_id, member_id, loaned_on) VALUES (5, 5, '2026-10-03') RETURNING id, book_id, member_id;

Show a solution

One way to solve it. Yours can look different and still pass the checks.

INSERT INTO members (name, email, city, joined)
VALUES ('Hedy', 'hedy@example.com', 'Vienna', '2026-10-03')
RETURNING id, name;

INSERT INTO loans (book_id, member_id, loaned_on)
VALUES (5, 5, '2026-10-03')
RETURNING id, book_id, member_id;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has loaded books, members and loans.
INSERT INTO members (name, email, city, joined)
VALUES ('Hedy', 'hedy@example.com', 'Vienna', '2026-10-03');

test.sql

-- test: Hedy is a member from Vienna, numbered 5 by PostgreSQL
SELECT EXISTS (SELECT 1 FROM members WHERE id = 5 AND name = 'Hedy' AND city = 'Vienna');

-- test: Her open loan of The Hobbit is loan 6
SELECT EXISTS (SELECT 1 FROM loans WHERE id = 6 AND book_id = 5 AND member_id = 5 AND loaned_on = '2026-10-03' AND returned_on IS NULL);

-- test: Both statements return the new rows
-- output:
--  id | name
-- ----+------
--   5 | Hedy
-- (1 row)
--
--  id | book_id | member_id
-- ----+---------+-----------
--   6 |       5 |         5
-- (1 row)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

-- PostgreSQL numbers the members and the loans itself (lesson B4.4).
CREATE TABLE members (
  id integer GENERATED ALWAYS AS IDENTITY,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members (name, email, city, joined) VALUES
  ('Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  ('Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  ('Linus', NULL, 'Berlin', '2026-02-10'),
  ('Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer GENERATED ALWAYS AS IDENTITY,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans (book_id, member_id, loaned_on, returned_on) VALUES
  (1, 1, '2026-09-01', '2026-09-10'),
  (3, 1, '2026-09-12', NULL),
  (2, 2, '2026-09-05', '2026-09-20'),
  (1, 3, '2026-09-15', NULL),
  (6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 2

Before and after

The library doubles every book that has only one copy. The starter does that but returns only the new copies. Make it return title, the copies before as before and after it as after. Then delete every returned loan (returned_on is not NULL), returning the id and book_id of each deleted loan.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    In an UPDATE, a plain copies in RETURNING is the new value. old.copies is the value before.

  2. Hint 2

    Give the columns their names with AS: old.copies AS before, new.copies AS after.

  3. Hint 3

    DELETE FROM loans WHERE returned_on IS NOT NULL RETURNING id, book_id;

Show a solution

One way to solve it. Yours can look different and still pass the checks.

UPDATE books
SET copies = copies * 2
WHERE copies = 1
RETURNING title, old.copies AS before, new.copies AS after;

DELETE FROM loans
WHERE returned_on IS NOT NULL
RETURNING id, book_id;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- Double the copies of every book with one copy.
UPDATE books
SET copies = copies * 2
WHERE copies = 1
RETURNING title, copies;

test.sql

-- test: The books now have 13 copies in all
SELECT sum(copies) = 13 FROM books;

-- test: Only the three open loans are left
SELECT count(*) = 3 AND count(returned_on) = 0 FROM loans;

-- test: The UPDATE shows before and after, the DELETE the deleted loans
-- output:
--     title    | before | after
-- -------------+--------+-------
--  Emma        |      1 |     2
--  Kindred     |      1 |     2
--  Beloved     |      1 |     2
--  Neuromancer |      1 |     2
-- (4 rows)
--
--  id | book_id
-- ----+---------
--   1 |       1
--   3 |       2
-- (2 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

-- PostgreSQL numbers the members and the loans itself (lesson B4.4).
CREATE TABLE members (
  id integer GENERATED ALWAYS AS IDENTITY,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members (name, email, city, joined) VALUES
  ('Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  ('Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  ('Linus', NULL, 'Berlin', '2026-02-10'),
  ('Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer GENERATED ALWAYS AS IDENTITY,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans (book_id, member_id, loaned_on, returned_on) VALUES
  (1, 1, '2026-09-01', '2026-09-10'),
  (3, 1, '2026-09-12', NULL),
  (2, 2, '2026-09-05', '2026-09-20'),
  (1, 3, '2026-09-15', NULL),
  (6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

RETURNING before WHERE

UPDATE loans
SET returned_on = '2026-10-03'
RETURNING *
WHERE id = 2;

What psql prints

ERROR:  syntax error at or near "WHERE"

Why, and the fix

RETURNING is the last clause of the statement. PostgreSQL reads RETURNING * as the end of the UPDATE and does not expect a WHERE after it. Move WHERE up: UPDATE loans SET returned_on = '2026-10-03' WHERE id = 2 RETURNING *. The same holds for DELETE … WHERE … RETURNING.

Giving a value to a GENERATED ALWAYS id

INSERT INTO members (id, name, email)
VALUES (5, 'Hedy', 'hedy@example.com')
RETURNING id;

What psql prints

ERROR:  cannot insert a non-DEFAULT value into column "id"

Why, and the fix

members.id is GENERATED ALWAYS AS IDENTITY: PostgreSQL chooses every id itself and refuses one you supply. Leave id out of the column list and let RETURNING id tell you which number the row got. psql adds a HINT about OVERRIDING SYSTEM VALUE; that is for copying existing data, not for everyday inserts.

old. after renaming it

UPDATE books
SET copies = copies + 1
WHERE id = 5
RETURNING WITH (OLD AS o, NEW AS n) old.copies, n.copies;

What psql prints

ERROR:  missing FROM-clause entry for table "old"

Why, and the fix

WITH (OLD AS o, NEW AS n) gives the old and new rows other names, and then the names old and new are hidden in that RETURNING list. Use the new names throughout: RETURNING WITH (OLD AS o, NEW AS n) o.copies, n.copies. Without the WITH part, old.copies and new.copies work as they are.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

RETURNING gives back the rows you changed

INSERT, UPDATE and DELETE normally report only how many rows they changed. Add RETURNING and a list at the very end of the statement, and the command returns one row for each row it changed. The list works like a select list: RETURNING id, title, an expression such as copies * 2 AS doubled, or RETURNING * for every column. A command that changes no row returns zero rows, not an error. The classic use is a value the database chooses. In setup.sql, members.id and loans.id are GENERATED ALWAYS AS IDENTITY (lesson B4.4 explains it): PostgreSQL numbers new rows itself, and INSERT … RETURNING id hands you the new number without a second query.

Which version of the row RETURNING sees

In an INSERT, RETURNING sees the row as it was inserted, so a column you left out shows its default or NULL. In an UPDATE it sees the new content: after SET copies = copies + 1, RETURNING copies shows the increased number. In a DELETE it sees the deleted row, one last time, so you can keep a record of what you removed. UPDATE writes every row its WHERE matches, even when SET leaves the value as it was, and RETURNING reports each of them. The rows come back in the order the command changed them; there is no ORDER BY in RETURNING, so do not rely on an order.

old and new, added in PostgreSQL 18

PostgreSQL 18 lets RETURNING say which version it means: old.copies is the value before the command, new.copies the value after it, and old.* or new.* give the whole row. So an UPDATE can show before and after side by side: RETURNING title, old.copies AS before, new.copies AS after. An INSERT has no old row, so its old values are NULL; a DELETE has no new row, so its new values are NULL. A plain column name keeps its default meaning. RETURNING WITH (OLD AS o, NEW AS n) o.copies, n.copies renames the two; old and new are then hidden, and old.copies fails with missing FROM-clause entry for table "old".

Sources

Last reviewed October 3, 2026