Articles
sql
SQL INSERT, UPDATE, DELETE like a pro with RETURNINGsql

You would recognise the following as a pretty typical SQL insert query with an extra line unlocking a powerful feature.

INSERT INTO some_table(name, address, description)
VALUES ("john", "location", "some description")
RETURNING *

Using RETURNING * allows you to easily view the data of the inserted row as a result of the insert query. This is especially useful for viewing the data in auto-generated columns or data which has been mutated by INSERT TRIGGERS.

The Benefit

Using RETURNING * you can now access the inserted, updated or deleted row's values. This is especially useful for chaining SQL querys or showing the value of the affected rows data.

Example

WITH inserted_row AS (
    INSERT INTO some_table(name, address, description)
    VALUES ("john", "location", "some description")
    RETURNING *
)
SELECT *
FROM inserted_row

The query result

id name address description
5 john location some description

This is especially useful if you wanted to use the generated autoincremented value in a follow up query.

Protip

You can specify only the columns you want in the following format

RETURNING column_1, column_2, column_3 etc