Use PostgreSQL `RETURNING` and `WITH` to return updated rows

At times one wants to return a DB row immediately after updating. What’s more, one may want to JOIN further data to the updated row. The RETURNING and WITH PostgreSQL extensions make this possible.

Let’s add some sample data:

CREATE TABLE birthdays
(
    name     TEXT,
    birthday DATE,
    age      SMALLINT
);

INSERT INTO birthdays
VALUES ('James', '1996-01-20', 22),
       ('Peter', '1990-06-18', 28),
       ('John', '1993-09-21', 25);

By using the RETURNING statement one can return any columns from updated rows. This query will return the name, birthday and age of all the updated rows:

-- Update age if it is incorrect
UPDATE birthdays
    SET age = date_part('year', age(birthday))
    WHERE date_part('year', age(birthday)) != age 
    RETURNING name, birthday, age;

WITH statements allow using the rows SELECTED by the query (in this case, an update) in another sub-query. Let’s do an implicit join on the updated birthday with the person’s favourite colour:

CREATE TABLE favourite_colour(name TEXT, colour TEXT);

INSERT INTO favourite_colour
VALUES ('James', 'Green'),
       ('Peter', 'Blue'),
       ('John', 'Magenta');
WITH updated AS (
    UPDATE birthdays
        SET age = date_part('year', age(birthday))
        WHERE date_part('year', age(birthday)) != age 
        RETURNING name, birthday, age)
SELECT updated.*, favourite_colour.colour
    FROM updated, favourite_colour
    WHERE updated.name = favourite_colour.name;

In the last statement the updated rows are added to the FROM clause, making their columns available for the SELECT and WHERE clauses.

Lastly, clean up:

DROP TABLE birthdays;
DROP TABLE favourite_colour

Go ahead, try it!