In PostgreSQL, the RETURNING clause is used with the UPDATE, DELETE, and INSERT queries to return the modified records. The RETURNING clause retrieves the modified data without executing a separate query to fetch that data and hence saves a lot of time and effort. The “RETURNING *” clause is used in PostgreSQL to get all the modified records.

This write-up presents a precise guide on how to retrieve the data from modified rows using the RETURNING clause in PostgreSQL. For better understanding, the below-mentioned concepts will be considered in this post:

• How to RETRIEVE Data From UPDATE Command?
• How to RETRIEVE Data From INSERT Command?
• How to RETRIEVE Data From DELETE Command?

So, let’s start with the UPDATE command.

How to RETRIEVE Data From UPDATE Command?

We have already created a table named “emp_data”, whose records are shown in the following snippet:

SELECT * FROM emp_data;

In the following snippet, we utilize the UPDATE command with RETURNING clause to modify and retrieve the employee name whose id is 3:

UPDATE emp_data
SET emp_name = 'Dean'
WHERE emp_id = 3
RETURNING *;

The output shows that the “RETURNING” clause successfully retrieves the updated row.

How to RETRIEVE Data From INSERT Command?

This example illustrates how to use the RETURNING clause with the Postgres INSERT query:

INSERT INTO emp_data(emp_id, emp_name, joining_date)
VALUES (8, 'Kane', CURRENT_DATE)
RETURNING *;

The output shows that one row has been inserted into the “emp_data” table. The newly inserted record has been retrieved using the “RETURNING” clause.

How to RETRIEVE Data From DELETE Command?

In the following snippet, we utilize the DELETE command with RETURNING clause to delete and retrieve the employee whose id is 7:

DELETE FROM emp_data
WHERE emp_id = 7
RETURNING *;

The output shows that one row has been successfully deleted from the “emp_data” table. The deleted record has been retrieved using the “RETURNING” clause.

Conclusion

In PostgreSQL, use the RETURNING clause with the UPDATE, DELETE, or INSERT commands to get/return the modified rows. The RETURNING clause retrieves the modified data without executing a separate query, so it saves a lot of time and effort. Use the “RETURNING *” clause to get all the modified records. This post explained how to return data from updated rows using the RETURNING clause in Postgres.