In Postgres, the “RETURNING” clause is used to return the newly inserted, deleted, or updated data. The working procedure of the RETURNING clause is similar to the “SELECT” statement. It is quite an interesting feature of PostgreSQL to verify the operations during execution time. The objective of this guideline is to demonstrate the usage of the RETURNING clause in Postgres.

The below-listed aspects will be explained in this post:

• Create a Sample Table in Postgres.
• Inserting Values Into a Table in Postgres.
• Example 1: Using RETURNING Clause with INSERT Statement
• Example 2: Using RETURNING Clause with DELETE Statement
• Example 3: Using RETURNING Clause with UPDATE Statement

Let's start the journey.

Create a Sample Table in PostgreSQL

First of all, create a table in the PostgreSQL database. Execute the below-given query to create a table named “team”:

CREATE TABLE team(
name VARCHAR(255),
age INTEGER);

On successful execution, the “team” table with two columns, “name” and “age”, has been created successfully.

Inserting Values Into a Table in PostgreSQL

The “INSERT INTO” statement is used for inserting values in the “team” table. The command that performs this insertion is as follows.

INSERT INTO team(name, age) 
VALUES ('Adam', '32');

In this table, two values, “Adam” and “32” are inserted in the “name” and “age” columns that can be confirmed from the resultant output.

Different examples are carried out in this write-up to explain the usage of the “RETURNING” clause in PostgreSQL.

Example 1: Using RETURNING Clause with INSERT Statement

An example is considered to return the values of a specific table column via the “RETURNING” clause. For instance, the statement is provided below:

INSERT INTO team(name , age) 
VALUES ('Adam', '32')
RETURNING name;

The output shows that the RETURNING clause retrieves the newly inserted value of the name column.

Specify the * after the RETURNING clause to get the newly inserted data of all the table columns:

INSERT INTO team (name, age) 
VALUES ('Molo', 27),
('King', 35) 
RETURNING *;

The output authenticates the working of the RETURNING Clause with the INSERT query.

Example 2: Using RETURNING Clause with DELETE Statement

In PostgreSQL, users can execute the DELETE Query with the “RETURNING” clause to retrieve the newly deleted records.

For displaying the data of the “team” table, execute the “SELECT” query as follows.

SELECT * FROM team;

Let’s delete some specific records from the “team” table using the DELETE query. The following statement explains how the "RETURNING" clause can be used to retrieve the deleted rows:

DELETE FROM team
WHERE name = 'Adam'
RETURNING *;

The above statement deletes all those records whose name is equal to “Adam”.

Example 3: Using RETURNING clause with UPDATE Statement

Another example is considered with the “UPDATE” statement to update specific information in the existing column. For instance, a value 30 is assigned to all those entities whose age is equal to or greater than 40. After that, the “RETURNING” clause is used to retrieve the updated names:

UPDATE team 
SET age= 30 
WHERE age>= 40 
RETURNING name, age AS new_age;

This is how you can retrieve the newly updated records using the RETURNING Clause.

Conclusion

In Postgres, the “RETURNING” clause is used with the INSERT, DELETE or UPDATE queries to retrieve the newly inserted, deleted, or updated data. It is useful to visualize the current operation by placing the “RETURNING” clause at the end of the statement. This article has explained all aspects of the “RETURNING” clause along with examples.