In PostgreSQL, the UPDATE query is used to update specific or all records of a table. Once the query is executed successfully, the user can confirm the updated records using the SELECT command. However, Postgres offers an UPDATE RETURNING clause that extends the functionalities of the standard UPDATE command and can be used to get the updated records.

This write-up will discuss what is UPDATE RETURNING and how to use it in PostgreSQL.

How to Use the UPDATE RETURNING Clause in PostgreSQL?

In PostgreSQL, the UPDATE RETURNING clause not only updates the selected rows but also retrieves the updated rows. The basic syntax of using the UPDATE RETURNING clause is illustrated in the below snippet:

UPDATE tab_name
SET col_name = val
WHERE condition
RETURNING *;

Here in the above syntax, the “RETURNING *” signifies that all the updated records will be retrieved on successful execution of the “UPDATE RETURNING” clause.

Example 1: How to Retrieve All Updated Records in Postgres?

Suppose we have a table named “emp_data” that contains the following records:

SELECT * FROM emp_data;

Suppose the user wants to update the joining date of all those employees whose id is greater than or equal to five. Also, the user wants to retrieve the updated records so that he can confirm the modified records. Both these functionalities can be achieved using a single “UPDATE RETURNING” clause as follows:

UPDATE emp_data
SET joining_date = CURRENT_DATE
WHERE id >= 5
RETURNING *;

In the above snippet, the CURRENT_DATE function is used within the UPDATE command to set today’s date as the joining date of employees whose id is greater than or equal to 5:

The output shows that the UPDATE RETURNING clause has successfully updated and retrieved the selected records.

Example 2: How to Retrieve Only Specific Columns From the Updated Records in Postgres?

The UPDATE RETURNING clause can be used to modify the selected records and retrieve only specific columns:

UPDATE emp_data
SET joining_date = CURRENT_DATE
WHERE emp_id = 4
RETURNING emp_name;

The above-stated query will update all those employees that fulfill the described condition. However, instead of returning all columns, the RETURNING clause will retrieve only the employee names from the “emp_name” column:

That was all about the use of the UPDATE RETURNING clause in Postgres.

Conclusion

In PostgreSQL, the UPDATE RETURNING clause not only updates the selected rows but also retrieves the modified rows. It saves a lot of time for the user as it modifies and retrieves the selected rows in one go. The UPDATE RETURNING clause can also be used to update the selected records and retrieve only specific columns. This post has explained the use of the UPDATE RETURNING clause in PostgreSQL using suitable examples.