RETURNING

PostgreSQL

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.

Oracle

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.