SQL Reference - RETURNING

RETURNING

RETURNING is a clause in SQL that is used to retrieve the rows affected by an INSERT, UPDATE, DELETE, or MERGE statement. This clause provides a way to get the data right after executing a data modification command, without needing to issue a separate SELECT statement. The returned rows are defined in the query and display the final state of the data after the operation.

Example

INSERT INTO person(first_name, last_name)
VALUES ('John', 'Doe')
RETURNING person_id;

Output

 person_id
-----------
         1

Explanation

The RETURNING clause in PostgreSQL allows to return the specific, updated row after an INSERT, UPDATE or DELETE operation. In this example, after inserting the new row (‘John’, ‘Doe’) into person table, the person_id of the new row is returned.

Example

INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe')
RETURNING id INTO :new_id;

Output

:new_id = 101

Explanation

The given SQL statement performs an insert operation into the employees table. After the insertion, it returns the id of the new tuple. This id is then stored in the :new_id bind variable. The result is that :new_id will contain the id of the newly inserted tuple. In this case, the id of the new employee is 101.