How to Return Data from Modified Rows in PostgreSQL

In PostgreSQL, we can use the RETURNING clause to return data that was modified during an UPDATE, INSERT, or DELETE operation. This allows us to see the data that was modified without having to perform a separate query to retrieve that data.

Example

Suppose we create a table as follows:

CREATE TABLE cats (
    catid serial primary key,
    catname varchar(60),
    color varchar(20),
    meal varchar(20)
);

The catid column uses the serial type, which will result in a unique identifier column. Any time we insert data into the table, this column will automatically generate a unique identifier.

We can use the RETURNING clause when inserting data to find out what that unique identifier is:

INSERT INTO cats( catname, color, meal ) 
VALUES 
    ( 'Meow', 'Yellow', 'Fish' ),
    ( 'Fluffy', 'White', 'Beef' ),
    ( 'Scratch', 'Yellow', 'Pork' ),
    ( 'Purr', 'Brown', 'Fish' ),
    ( 'Furball', 'Black', 'Beef' ),
    ( 'Scarcat', 'Yellow', 'Beef' ),
    ( 'Rolly', 'Black', 'Pork' )
RETURNING catid;

Result:

 catid 
-------
     1
     2
     3
     4
     5
     6
     7
(7 rows)

INSERT 0 7

In this case, we inserted seven rows. The unique identifier started at 1 and incremented by 1.

And if we insert a new row, we can get its unique identifier too:

INSERT INTO cats( catname, color, meal ) 
VALUES 
    ( 'Nap', 'Black', 'Pork' )
RETURNING catid;

Result:

 catid 
-------
     8
(1 row)

Updates

The RETURNING clause can be handy during data updates:

UPDATE cats 
SET meal = 'Fish'
WHERE color = 'Yellow'
RETURNING catname, meal AS new_meal;

Result:

 catname | new_meal 
---------+----------
 Meow    | Fish
 Scratch | Fish
 Scarcat | Fish
(3 rows)

UPDATE 3

Deletes

And we can use the RETURNING clause to return the rows that were deleted when using the DELETE statement:

DELETE FROM cats
WHERE color = 'Black'
RETURNING *;

Result:

 catid | catname | color | meal 
-------+---------+-------+------
     5 | Furball | Black | Beef
     7 | Rolly   | Black | Pork
     8 | Nap     | Black | Pork
(3 rows)

DELETE 3

In this case three rows were deleted. We used the * wildcard to return all columns.

Let’s take a look at our table now:

SELECT * FROM cats
ORDER BY catid ASC;

Result:

 catid | catname | color  | meal 
-------+---------+--------+------
     1 | Meow    | Yellow | Fish
     2 | Fluffy  | White  | Beef
     3 | Scratch | Yellow | Fish
     4 | Purr    | Brown  | Fish
     6 | Scarcat | Yellow | Fish
(5 rows)