SQL INSERT, UPDATE, DELETE like a pro with RETURNING
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