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)